Forum Discussion
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 and two conditions to pull a subset of Table1.
FILTER(Table2,(Table2[Col1]=50)*(Table2[Col2]=30),"")
No problem.
Then I want to return the value of Column 4 based on a lookup of values in Column 3. Suppose I save this in columns G to J
I could use
=XLOOKUP($B$11,I20:I22,J20:J22,"",-1,1)
The problem is that I am not putting this subset anywhere. I want to do this all in one formula because the output is a cell in a table. Is there a way to refer to a "virtual" table in the same formula?
I also tried this formula
=XLOOKUP($B$11,FILTER(Table2[Col3],(Table2[Col1]=50)*(Table2[Col2]=30),""),FILTER(Table2[Col4],(Table2[Col1]=50)*(Table2[Col2]=30),""),"",-1,1)
But now I feel like I'm in a Godel, Escher, Bach story that has gone horribly wrong.
Maybe what I'm trying to do makes sense. Maybe it can be done. If so, I'm sure someone here knows how.
Chuck
- 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)
11 Replies
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
- 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)- Thank you for the response. This does seem to work! I had not heard of the LET function. It's the assignment operator that I was looking for. Thank you!
I say that it seems to work because when I changed the formula the output didn't change. That's a good thing. 🙂
Thank you again.
- Hmm, after posting, I found an error in the formula. I fixed it in the post and then in the spreadsheet. It seems to have worked. I am verifying.
What do you think? Should it have worked? I didn't post a sample spreadsheet. If that's necessary, then I can create one.- If you've got it working, no need. But for curiosity's sake, I'd sure love to see it in action, and your posting it would save my having to create it.
I like Matt Tarler's use of LET as well.- Yes, I do have it working. I might create a spreadsheet, but given that it's Friday afternoon, the odds are low. 🙂
Thank you for the support.
Chuck
- yes I thought that [@[4]] was wrong and otherwise thought it should work, but as I noted you should consider the LET statement. I think it makes things easier and cleaner.
Now that I've read the entire post (not just the reference to Hofstadter's book), I get the "down the rabbit hole and through the looking glass" notion....
I want to do this all in one formula because the output is a cell in a table. Is there a way to refer to a "virtual" table in the same formula?
Yes, but maybe also no. I know I've nested FILTER functions within an outer function to do such things as COUNT....or SUM
I also tried this formula
=XLOOKUP($B$11,FILTER(Table2[Col3],(Table2[Col1]=50)*(Table2[Col2]=30),""),FILTER(Table2[Col4],(Table2[Col1]=50)*(Table2[Col2]=30),""),"",-1,1)
This, however, might be pushing things too far. But intriguing, to be sure.
Is it possible for you to post the workbook, if not here in the forum, then on OneDrive or GoogleDrive (or one of the other cloud services) and then post a link here granting access?
statman wrote, apparently expecting people to grok the reference:
But now I feel like I'm in a Godel, Escher, Bach story that has gone horribly wrong.
Now, I have to say, I even have the book but don't get the reference!! (Of course, I've only read parts of it; one of many like that, intellectually fascinating--to a point--but not enough to keep me going. Maybe after this I'll pick it up again.)
- Yes, great book! I read it in the 80's. It did take me a couple of attempts to get all the way through it. 🙂
