# IF Formula

#### Roopen

##### Board Regular
Hi All,

As always, I am after your help....

I have in column H either "ESPS" "LDCS" "NGDB" and in another workbook (called Assumptions I have the above with say "ESPS = 20%" LDCS = "11% and "NGDB = 30%" Assume cells A1 A2 A3

I want a formula that will state that if the cell in H states ESPS it will multiply column B by 20% (from the assumptions workbook)

Hope the above makes sense

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Mr_Roscoe

##### Board Regular
Roopen - maybe not an IF formula but a VLOOKUP.

Create a small table in the Assumptions workbook where:

A1 = ESPS, A2 = LDCS, A3 = NGDB and B1 = 20%, B2 = 11%, B3 = 30%

Then you formula in cell H2 will be:

=B2*VLOOKUP(H2,Assumptions!\$A\$1:\$B\$3,2,0)

trust this helps, Ian R.

#### xld

##### Banned
Try this array formula

=B2*MID(INDEX([Book2]Sheet1!\$A1:\$A10,MIN(IF(ISNUMBER(FIND(H2,[Book2]Sheet1!\$A1:\$A10)),ROW([Book2]Sheet1!\$A1:\$A10)))),
FIND("= ",INDEX([Book2]Sheet1!\$A1:\$A10,MIN(IF(ISNUMBER(FIND(H2,[Book2]Sheet1!\$A1:\$A10)),ROW([Book2]Sheet1!\$A1:\$A10)))))+2,99)

#### Roopen

##### Board Regular
Hi Ian,

The formula works a treat. Thank you ever so much

Regards

Roopen

#### Roopen

##### Board Regular
XLD,

Thank you also, the formula is huge but also works a treat...

Appreciate the swift response

Regards

Roopen

Replies
10
Views
182
Replies
18
Views
205
Replies
7
Views
167
Replies
14
Views
224
Replies
0
Views
27

1,133,277
Messages
5,657,799
Members
418,414
Latest member
ECMdusty

### 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