Forum Discussion
Solved
Filter in Xlookup function
Hello, [Excel 365 v2208; Windows 10] I am trying to hurt my brain with this combination. Your help (to avoid the hurt) is appreciated. đŸ™‚ Situation: Table2 is 4 columns and 150 rows. I use FILTER...
- you could use the LET() statement and I recommend you try that statement out.
=LET(subset,FILTER(Table2,(Table2[Col1]=50)*(Table2[Col2]=30),""),
XLOOKUP($B$11,INDEX(subset,,3),INDEX(subset,,4),"",-1,1)
Much as I like FILTER, it does not appear to be necessary for the problem you outline. Simply blanking out the lookup array should restrict the search.
= XLOOKUP(
lookupValue,
IF((Table1[Col1]=50)*(Table1[Col2]=30), Table1[Col3]),
Table1[Col4]
)LET could also be used but doesn't make that much difference in the present case.
= LET(
lookupArray, IF((Table1[Col1]=50)*(Table1[Col2]=30), Table1[Col3]),
returnArray, Table1[Col4],
XLOOKUP(lookupValue, lookupArray, returnArray)
)[ I am sure more meaningful names are possible! ]
PeterBartholomew1, nice alternative. I guess I would have been worried that a 'FALSE' value from the IF statement might cause a problem with the LOOKUP especially if the values are in that -1 or 0 range that XL may use as the internal value for FALSE. nice to know it isn't a problem