# Formula to multiply a value by a factor that is based on the word found in another column

I would like a Google Sheets formula in column C that checks what word is in column A and then multiplies the value in column B by a factor selected based on the matched word.

For example, if the matched word is:

1. Cars, then factor is 1;
2. Trucks, then factor is 2;
3. Bikes, then factor is 3
A B C
1 Type Amount Desired Result
2 Trucks 0.4 0.8
3 Cars 0.3 0.3
4 Trucks 0.7 1.4
5 Bikes 0.5 1.5

There are different approaches depending on the number of words you expect to have:

# MATCH

This will return a factor incrementing by 1 depending on the position of the word in a hardcoded list or E2:E4:

Single Row Versions
------------------------
=IFNA(B2 * MATCH(A2, {"Cars"; "Trucks"; "Bikes"}, 0)))

=IFNA(B2 * MATCH(A2, $E$2:$E$4, 0)))   # range ref. for words

Array Versions
-----------------
=INDEX(IFNA(B2:B * MATCH(A2:A, {"Cars"; "Trucks"; "Bikes"}, 0)))

=INDEX(IFNA(B2:B * MATCH(A2:A, $E$2:$E$4, 0)))   # range ref. for words

# VLOOKUP

Use a lookup table of hardcoded column pairs with words in one column and factors in the other or a range reference to words E2:E4 and factors in F2:F4:

Single Row Versions
------------------------
=IFNA(B2 * VLOOKUP(A2, {"Cars", 1; "Trucks", 2; "Bikes", 3}, 2, 0))

=IFNA(B2 * VLOOKUP(A2, $E$2:$F$4, 2, 0))   # range ref. for words

Array Versions
-----------------
=INDEX(IFNA(B2:B * VLOOKUP(A2:A, {"Cars", 1; "Trucks", 2; "Bikes", 3}, 2, 0)))

=INDEX(IFNA(B2:B * VLOOKUP(A2:A, $E$2:$F$4, 2, 0)))   # range ref. for words