Sep 16 2022 09:37 AM - edited Sep 16 2022 09:56 AM
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
Sep 16 2022 09:56 AM
@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.)
Sep 16 2022 10:02 AM
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?
Sep 16 2022 10:05 AM
Sep 16 2022 10:07 AM
SolutionSep 16 2022 10:10 AM
Sep 16 2022 10:10 AM
Sep 16 2022 10:26 AM
Sep 16 2022 10:38 AM
Sep 16 2022 10:55 AM
Sep 16 2022 12:25 PM
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! ]
Sep 16 2022 01:53 PM
Sep 16 2022 10:07 AM
Solution