condense formula

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,471
Office Version
  1. 2016
Platform
  1. Windows
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")
 
Solution

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,471
Office Version
  1. 2016
Platform
  1. Windows
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")
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,471
Office Version
  1. 2016
Platform
  1. Windows
...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")
 

Watch MrExcel Video

Forum statistics

Threads
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.
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 "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
Top