PDA

View Full Version : [SOLVED] 4 way Lookup but last lookup is a function query?

RINCONPAUL
07-16-2017, 11:57 PM
I have an index match array formula:

{=IFERROR(INDEX(BD\$2:BD\$902,MATCH(AV\$2&AW\$2&AV5,BA\$2:BA\$902&BB\$2:BB\$902&BC\$2:BC\$902,0)),"")}

At this point it's a 3 way lookup. I want to introduce a 4th column to lookup (BE\$2:BE\$902). However rather than each cell being filled with a static value, I want to introduce a different ">=" or "<=" a numeric value in each column cell. Example BE\$2 is >=2.0, BE\$3 is <=3.4,.....

I know I could do it by introducing a horizontal index of values but there would be 100's. Any ideas welcome.

mdmackillop
07-17-2017, 03:06 AM
Can you provide sample data for you 3 and 4 way lookups.

RINCONPAUL
07-17-2017, 04:06 AM
mdm.. The 4th lookup value is PRICE in col AU\$5:AU\$22. This is referenced in col BD\$2:BD\$902 and then indexed in col BE\$2:BE\$902. For the attached example, cell AW5 should return "LAY".
Thanks

shrivallabha
07-17-2017, 05:22 AM
Possibly (Normally entered):
=LOOKUP(2,(1/((\$BA\$2:\$BA\$23=\$AV\$2)*(\$BB\$2:\$BB\$23=\$AW\$2)*(\$BC\$2:\$BC\$23=\$AV5)*COUNTIF(\$AU5 ,\$BD\$2:\$BD\$23))),\$BE\$2:\$BE\$23)
copy down

mdmackillop
07-17-2017, 06:08 AM
No good with these complex formula but a simple change to INDEX(BE\$2:BE\$902, returns the LAY from row 4. Doesn't fill down though.

RINCONPAUL
07-17-2017, 11:30 AM
shrivallabha, that does the trick. Well done and without an array formula.
Cheers :clap: