Lookup from 4 different tables

[Manny Singh]

Hi All,

Could you please help writing an efficient formula, I have explained the query in attached file.

Thanks,
Manny

 

[vletm]

Manny Singh
As written in yellow cells...

 

[bosco_yip]

1] As per your file, put this in F5, copied down :

=IFERROR(VLOOKUP(B5&"-"&C5,INDEX(Cost!C$2:C$33,ISODD(RIGHT(E5))*16+(LEFT(A5)="C")*8):Cost!E$33,3,0),"")

2] Compared Column F results with your Column D values.

Regards
Bosco

 

[GraH - Guido]

Perhaps less "complicated" alternative, if you don't mind 4 helper columns (G -> J) to replace 4 nested ifs:
=IFERROR( INDEX ( (Ref_1,Ref_2,Ref_3,Ref_4);MATCH(B5&"-"&C5 , Cost!$C$3:$C$9 , 0 ) , 3 , MAX(G5:J5) ) ,"" )

Ref_1 .. Ref_4 are your 4 lookup tables.

You could also use a formula in the name manager/helper column that returns the max of your nested ifs and use that in the index formula of type Reference.

But cannot beat Bosco's for shortness.

 

[Manny Singh]

Perhaps less "complicated" alternative, if you don't mind 4 helper columns (G -> J) to replace 4 nested ifs:
=IFERROR( INDEX ( (Ref_1,Ref_2,Ref_3,Ref_4);MATCH(B5&"-"&C5 , Cost!$C$3:$C$9 , 0 ) , 3 , MAX(G5:J5) ) ,"" )

Ref_1 .. Ref_4 are your 4 lookup tables.

You could also use a formula in the name manager/helper column that returns the max of your nested ifs and use that in the index formula of type Reference.

But cannot beat Bosco's for shortness.

Thanks Mate, simple but effective, much appreciated.

 

[Manny Singh]

1] As per your file, put this in F5, copied down :

=IFERROR(VLOOKUP(B5&"-"&C5,INDEX(Cost!C$2:C$33,ISODD(-RIGHT(E5))*16+(LEFT(A5)="C")*8):Cost!E$33,3,0),"")

2] Compared Column F results with your Column D values.

Regards
Bosco

Hi Bosco,
I hope one day I will be able to write formulas like you, do you mind explaining what's going on inside Index part?
Much appreciated.

 

[bosco_yip]

Perhaps less "complicated" alternative, if you don't mind 4 helper columns (G -> J) to replace 4 nested ifs:
=IFERROR( INDEX ( (Ref_1,Ref_2,Ref_3,Ref_4);MATCH(B5&"-"&C5 , Cost!$C$3:$C$9 , 0 ) , 3 , MAX(G5:J5) ) ,"" )

Ref_1 .. Ref_4 are your 4 lookup tables.

You could also use a formula in the name manager/helper column that returns the max of your nested ifs and use that in the index formula of type Reference.

But cannot beat Bosco's for shortness.

Hi GraH - Guido,

IF you wanted to use Index(), the OP's 4 Lookup tables layout can be remained and need not helper columns, the formula as in :

In F5, copied down :

=IFERROR(INDEX((Cost!E$2:E$9,Cost!E$10:E$17,Cost!E$18:E$25,Cost!E$26:E$33),MATCH(B5&"-"&C5,Cost!C$2:C$9,0),,ISODD(RIGHT(E5))*2+(LEFT(A5)="C")+1),"")

Regards
Bosco

 

[bosco_yip]

Hi Bosco,
I hope one day I will be able to write formulas like you, do you mind explaining what's going on inside Index part?
Much appreciated.

Hi Manny Singh,

The Index part : .....INDEX(Cost!C$2:C$33,ISODD(RIGHT(E5))*16+(LEFT(A5)="C")*8)………

This part : ISODD(RIGHT(E5))*16+(LEFT(A5)="C")*8

Which is a 2 criteria logical and mathematical calculations returns : 0, 8, 16 or 24, to meet with your Lookup table for every 8 rows per one zone.

Then, the INDEX() will give the 1st cell address of each Lookup table zones

Regards
Bosco

 

[Manny Singh]

Hi Manny Singh,

The Index part : .....INDEX(Cost!C$2:C$33,ISODD(RIGHT(E5))*16+(LEFT(A5)="C")*8)………

This part : ISODD(RIGHT(E5))*16+(LEFT(A5)="C")*8

Which is a 2 criteria logical and mathematical calculations returns : 0, 8, 16 or 24, to meet with your Lookup table every 8 rows per one zone.

Then, the INDEX() give the 1st cell address of the 4 Lookup table zones

Regards
Bosco

Brilliant, Thanks Bosco, this forum is so good because of Ninjas like you.

 

[GraH - Guido]

Hi GraH - Guido,

IF you wanted to use Index(), the OP's 4 Lookup tables layout can be remained and need not helper columns, the formula as in :

In F5, copied down :

=IFERROR(INDEX((Cost!E$2:E$9,Cost!E$10:E$17,Cost!E$18:E$25,Cost!E$26:E$33),MATCH(B5&"-"&C5,Cost!C$2:C$9,0),,ISODD(RIGHT(E5))*2+(LEFT(A5)="C")+1),"")

Regards
Bosco

Thx, Bosco. Not only for this gentle tip. But for all of your formula wit and creativity you display here. invented the word awe-sum for that. You do set the bar high, sir.