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

Alberto Excel

New Member
Jul 2, 2012
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.
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)
Upvote 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;
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.
Upvote 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!
Upvote 0
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
Upvote 0
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:
Upvote 0
First create a table with all country of provenances in

A 2
E 2.5
C 1.7

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

Last edited:
Upvote 0

Forum statistics

Latest member

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
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
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