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: