Forum Discussion

statman's avatar
statman
Copper Contributor
Sep 16, 2022
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...
excel
Formulas and Functions
office 365
  • mtarler's avatar
    mtarler
    Sep 16, 2022
    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)
mathetes's avatar
mathetes
Silver Contributor
Sep 16, 2022

statman 

 

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?

Resources