Forum Discussion
Xlookup multiple match criteria
Hi - I would like to use a look up table to return an exact match based on multiple criteria (like an AND nesting in an IF function). My data does not have a single key so I need to match 3 differen...
SusanJackson You may try FILTER() function.
=FILTER($L$2:$L$9,($I$2:$I$9=A2)*($J$2:$J$9=B2)*($K$2:$K$9=C2))For dynamic spill array use FILTER() with MAP() function.
=MAP(A2:A9,B2:B9,C2:C9,LAMBDA(x,y,z,FILTER(L2:L9,(I2:I9=x)*(J2:J9=y)*(K2:K9=z))))See the attached file.
Thank you Harun! this is also a simple way for the test model. I was able to use the FILTER() formula and modify it to look at the look up table in another worksheet, however, I received a SPILL error when using FILTER () and MAP () to reference the LookUp table in another worksheet though. I am a basic user so any feedback is welcome on this error