Using IF THEN formula in order to multiply values by fixed cells

[Alberto Excel]

Dear All,
I am trying to create a quotation where I multiply my costs to different values depending on where the merchandise comes from, using Excel 2010.

In the first column I would put my cost price.
In the second column I would put the country of provenance. I.E. A for America, E for Europe, and C for China.
In three separate cells I would put the multiplying coefficient for each region. I.E. American prices would be multiplied by 2, European by 2.5, and Chinese by 1.7

I would like to create a formula where if in the column of provenance there is i.e. E (for Europe,) the cost in the first column would be multiplied by 2.5, and so on and so forth for each value in each cell of the first column.

Does anybody have any idea how to do this? I would be very grateful for any suggestions. Thanks!

Please also understand my knowledge of Excel is limited and I may be ignorant of other options available to solve this problem.

 

[VoG]

Try like this


=A1*LOOKUP(B1,{"A","C","E"},{2,1.7,2.5})

 

[BenMiller]

Assuming you have your list of the multiplying coeffiecents in cells D1:E3 (just for this example, with A, E, and C), set up like this: D1: A E1: 2 D2: E E2: 2.5​ etc., use this formula in C1 and drag it down: =A1*VLOOKUP(B1,D:E,2,0)

 

[Alberto Excel]

When I copy that formula and press enter a window appears with "The formula you typed contains an error."
Hitting Ok, the cursor highlights in black B1, and under I get a writing which states;
LOOKUP(lookup_value;lookup_vector;[result_vector])
Please note that I'm living in Saudi Arabia and bought my computer there 6 months ago. I don't know if that may make a difference.

 

[martindwilson]

change the separater from , to ;
=A1*LOOKUP(B1;{"A";"C";"E"};{2;1.7;2.5}) or =A1*VLOOKUP(B1;D:E;2;0)

 

[Alberto Excel]

I've tried the second formula A1*VLOOKUP(B1;D:E;2;0) and it works perfectly. THANK YOU!!!!

This may be a bit too much to ask, but I'm trying to understand the formula and maybe someone can explain it to me. I understand the B1; but what does D:E;2;0 mean? What do that parameters mean to the formula? I suppose D:E will look for one letter matched to one number, but what about the 2;0?

Thanks again for all the help!

 

[martindwilson]

d:e is the 2 column range you are looking down d when it finds an exact match(thats what the 0 is ,but it really should be "false" as per the help files,but for excel purposes 0=false 1=true) it returns the value from the second column that's the 2 so if you had VLOOKUP(B1;D:g;3;0) it would return from the 3rd column in the range D:G which would be col F

 

[Jeff Meyers]

Here's the sytax of the VLOOKUP function:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

D:E is the table_array where the lookup_value (B1) will be located. VLOOKUP will look in the first column of the table_array for the lookup_value.

2 is the column index number. Once the lookup_value is found in the first column, this number will specify which column to return the value from. In your case it is column 2.

0 is the range_lookup parameter. This specifices whether to look for an exact match (FALSE or 0) or an approximate match (TRUE or 1).

Here's a link to a resource with more info on VLOOKUP:

http://www.contextures.com/xlFunctions02.html

 

[Skybluekid]

First create a table with all country of provenances in

A 2
E 2.5
C 1.7

A B C
Cost Provience price
20 E =(VLOOKUP(B1,Table,2,0)*A1


Then

 

[martindwilson]

help files show true/false putting 1/0 is not a help