condense formula

[orsm6]

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.

 

[Toadstool]

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
	B	C	D
5	SA001212		VLOOKUP1
6	SA000456		VLOOKUP1
7	30002830		VLOOKUP1
8	SA001559		VLOOKUP2
9	SA001311		VLOOKUP2
10	30012771		VLOOKUP2
11			VLOOKUP3
12	XYZ		VLOOKUP3
Sheet1

Cell Formulas
Range	Formula
D5:D12	D5	=CHOOSE(IFERROR(INT(MATCH(B5,{"SA001212","SA000456","30002830","SA001559","SA001311","30012771"},0)/4)+1,3),"VLOOKUP1","VLOOKUP2","VLOOKUP3")

 

[orsm6]

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
	B	C	D
5	SA001212		VLOOKUP1
6	SA000456		VLOOKUP1
7	30002830		VLOOKUP1
8	SA001559		VLOOKUP2
9	SA001311		VLOOKUP2
10	30012771		VLOOKUP2
11			VLOOKUP3
12	XYZ		VLOOKUP3
Sheet1

Cell Formulas
Range	Formula
D5:D12	D5	=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]

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
	B	C	D
5	SA001212		VLOOKUP1
6	SA000456		VLOOKUP1
7	30002830		VLOOKUP1
8	SA001559		VLOOKUP2
9	SA001311		VLOOKUP2
10	30012771		VLOOKUP2
11			VLOOKUP3
12	XYZ		VLOOKUP3
Sheet1

Cell Formulas
Range	Formula
D5:D12	D5	=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]

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

 

[Toadstool]

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

ORSM6-v2.xlsx
	B	C	D
5	SA001212		VLOOKUP1
6	SA000456		VLOOKUP1
7	30002830		VLOOKUP1
8	SA001559		VLOOKUP2
9	SA001311		VLOOKUP2
10	30012771		VLOOKUP2
11			VLOOKUP3
12	XYZ		VLOOKUP3
Sheet1 (2)

Cell Formulas
Range	Formula
D5:D12	D5	=CHOOSE(IFERROR(INT(MATCH(B5&"",{"SA001212","SA000456","30002830","SA001559","SA001311","30012771"},0)/4)+1,3),"VLOOKUP1","VLOOKUP2","VLOOKUP3")

 

[Toadstool]

...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
	B	C	D	E	F	G	H	I
1							Code	VLOOKUP No.
2							SA001212	1
3							SA000456	1
4							30002830	1
5	SA001212		VLOOKUP1				SA001559	2
6	SA000456		VLOOKUP1				SA001311	2
7	30002830		VLOOKUP1				30012771	2
8	SA001559		VLOOKUP2				SA090909	4
9	SA001311		VLOOKUP2				3999999	4
10	30012771		VLOOKUP2				SA393939	4
11			VLOOKUP3
12	XYZ		VLOOKUP3
13	SA090909		VLOOKUP4
14	3999999		VLOOKUP4
15	SA393939		VLOOKUP4
Sheet1 (2)

Cell Formulas
Range	Formula
D5:D15	D5	=CHOOSE(IFERROR(INDEX($I$2:$I$99,MATCH(B5,$H$2:$H$99,0)),3),"VLOOKUP1","VLOOKUP2","VLOOKUP3","VLOOKUP4")