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

#### Alberto Excel

##### New Member
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.

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try like this

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

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)

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.

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

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!

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

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).

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

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

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

Replies
23
Views
556
Replies
10
Views
280
Replies
0
Views
198
Replies
4
Views
190
Replies
1
Views
158

1,203,075
Messages
6,053,394
Members
444,661
Latest member
liamoohay

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back