# condense formula

#### orsm6

##### Active Member
Hi all - this formula below works well, but it is likely far too messy and wondering if anyone is able to simplify it.

formula:
=IFERROR(IF(A11=0,"",IF(A11="","",IF(ISNUMBER(SEARCH("SA001212",B11)),VLOOKUP("Material",CK13b!\$C\$16:\$S\$100,16,0)/SUM(\$N\$68/1000),IF(ISNUMBER(SEARCH("SA000456",B11)),VLOOKUP("Material",CK13b!\$C\$16:\$S\$100,16,0)/SUM(\$N\$68/1000),IF(ISNUMBER(SEARCH("30002830",B11)),VLOOKUP("Material",CK13b!\$C\$16:\$S\$100,16,0)/SUM(\$N\$68/1000),IF(ISNUMBER(SEARCH("SA000450",B11)),VLOOKUP("Material",CK13c!\$C\$16:\$S\$100,16,0)/SUM(\$N\$76/1000),IF(ISNUMBER(SEARCH("Slurry",C11)),VLOOKUP("Material",CK13c!\$C\$16:\$R\$100,16,0)/SUM(\$N\$76/1000),INDEX(CK13a!\$P\$16:\$P\$100,MATCH(1,INDEX((B11=CK13a!\$L\$16:\$L\$100)*(CK13a!\$P\$16:\$P\$100>0),0,1),0))))))))),0)

Assume formula is in cell E5 and in the same formula i need to do 1 of 3 things:
- if the number in B5 is: SA001212, SA000456, 30002830 are found then VLOOKUP("Material",CK13b!\$C\$16:\$S\$100,16,0)/SUM(\$N\$68/1000)
- if the number in B5 is: SA001559, SA001311, 30012771 are found then VLOOKUP("Material",CK13c!\$C\$16:\$S\$100,16,0)/SUM(\$N\$76/1000)
- if none of the above are found: =VLOOKUP(B5,CK13a!L18:S24,5,0)

i have tried this formula that works.... but i do not know how to expand on it to do all 3 things i need
=IFERROR(LOOKUP(2^15,SEARCH({"SA001212","SA000456","3002830"},B5),VLOOKUP("Material",CK13b!\$C\$16:\$S\$100,16,0)/SUM(\$N\$68/1000)),"no")

thanks for any help offered.

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

##### Well-known Member
Hi Orsm6,

I'm not going to build the multi sheet test data but if you substitute your 3 VLOOKUP options then this should work

ORSM6-v2.xlsx
BCD
5SA001212VLOOKUP1
6SA000456VLOOKUP1
730002830VLOOKUP1
8SA001559VLOOKUP2
9SA001311VLOOKUP2
1030012771VLOOKUP2
11VLOOKUP3
12XYZVLOOKUP3
Sheet1
Cell Formulas
RangeFormula
D5:D12D5=CHOOSE(IFERROR(INT(MATCH(B5,{"SA001212","SA000456","30002830","SA001559","SA001311","30012771"},0)/4)+1,3),"VLOOKUP1","VLOOKUP2","VLOOKUP3")

• orsm6

#### orsm6

##### Active Member
Hi Orsm6,

I'm not going to build the multi sheet test data but if you substitute your 3 VLOOKUP options then this should work

ORSM6-v2.xlsx
BCD
5SA001212VLOOKUP1
6SA000456VLOOKUP1
730002830VLOOKUP1
8SA001559VLOOKUP2
9SA001311VLOOKUP2
1030012771VLOOKUP2
11VLOOKUP3
12XYZVLOOKUP3
Sheet1
Cell Formulas
RangeFormula
D5:D12D5=CHOOSE(IFERROR(INT(MATCH(B5,{"SA001212","SA000456","30002830","SA001559","SA001311","30012771"},0)/4)+1,3),"VLOOKUP1","VLOOKUP2","VLOOKUP3")
Hi Toadstool, this is awesome..... so far it is working just fine thank you very much.

if i wanted more vlookups i can simply add them in??

#### orsm6

##### Active Member
Hi Orsm6,

I'm not going to build the multi sheet test data but if you substitute your 3 VLOOKUP options then this should work

ORSM6-v2.xlsx
BCD
5SA001212VLOOKUP1
6SA000456VLOOKUP1
730002830VLOOKUP1
8SA001559VLOOKUP2
9SA001311VLOOKUP2
1030012771VLOOKUP2
11VLOOKUP3
12XYZVLOOKUP3
Sheet1
Cell Formulas
RangeFormula
D5:D12D5=CHOOSE(IFERROR(INT(MATCH(B5,{"SA001212","SA000456","30002830","SA001559","SA001311","30012771"},0)/4)+1,3),"VLOOKUP1","VLOOKUP2","VLOOKUP3")
I have come across one issue so far....

when the number is a number... such as 30012771 it does not perform the right vlookup. in this case it performs vlookup 3 it should have done vlookup 2

#### orsm6

##### Active Member

Ignore that.... i just realised i had to take the number out of the ""... so from "30012771" to just 30012771

##### Well-known Member
I suspect it's treated as numeric but you can add a null after the B5 to force it as text

ORSM6-v2.xlsx
BCD
5SA001212VLOOKUP1
6SA000456VLOOKUP1
730002830VLOOKUP1
8SA001559VLOOKUP2
9SA001311VLOOKUP2
1030012771VLOOKUP2
11VLOOKUP3
12XYZVLOOKUP3
Sheet1 (2)
Cell Formulas
RangeFormula
D5:D12D5=CHOOSE(IFERROR(INT(MATCH(B5&"",{"SA001212","SA000456","30002830","SA001559","SA001311","30012771"},0)/4)+1,3),"VLOOKUP1","VLOOKUP2","VLOOKUP3")

• orsm6

##### Well-known Member
...but to answer your other question: If you want to add other options you'll need to work out the maths to get the right option for the CHOOSE.

It may be better to have an actual lookup table (which can be hidden or on another sheet) and give the VLOOKUP number there. Note that the Code would need to be in the appropriate text/numeric format.

e.g. here I've added a fourth lookup option if the code is text SA090909, numeric 3999999 or text SA393939.

ORSM6-v2.xlsx
BCDEFGHI
1CodeVLOOKUP No.
2SA0012121
3SA0004561
4300028301
5SA001212VLOOKUP1SA0015592
6SA000456VLOOKUP1SA0013112
730002830VLOOKUP1300127712
8SA001559VLOOKUP2SA0909094
9SA001311VLOOKUP239999994
1030012771VLOOKUP2SA3939394
11VLOOKUP3
12XYZVLOOKUP3
13SA090909VLOOKUP4
143999999VLOOKUP4
15SA393939VLOOKUP4
Sheet1 (2)
Cell Formulas
RangeFormula
D5:D15D5=CHOOSE(IFERROR(INDEX(\$I\$2:\$I\$99,MATCH(B5,\$H\$2:\$H\$99,0)),3),"VLOOKUP1","VLOOKUP2","VLOOKUP3","VLOOKUP4")

• orsm6

Replies
3
Views
57
Replies
1
Views
324
Replies
0
Views
64
Replies
0
Views
63
Replies
8
Views
111

### Forum statistics

1,130,425
Messages
5,642,042
Members
417,251
Latest member
Dordrecht ### 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