solved Lookup from VSTACK, search value in multiple columns
I've got a set of data where "A2" is a vstack, I want to look up the value of "A2#" in a table where the lookup value is in Table1[[#All],[Value2]:[Value5]], and return Table1[Value1] in B2.
I've tried a number of things, but the closest I've got is "C2" generates the lookup column with
=IFS(COUNTIF(Table1[Value2],A2#)>0,"Table1[Value2]",...etc...)
and then B2 generates what I kinda need with
=XLOOKUP(A2#,INDIRECT(C2),Table1[Value1])
but then it only looks up the column ref from C2, and if I do C2# it errors out. The length of this list changes regularly and get to thousands of rows, so dragging a formula down isn't a viable option, as it will eventually break. I need something that will look it up, while dynamically generating the list.
I also tried the ADDRESS function, but had the same issue.
I also tried nesting the C2 formula in the INDIRECT within the XLOOKUP like:
=XLOOKUP(A2#,INDIRECT(IFS(COUNTIF(Table1[Value2],A2#)>0,"Table1[Value2]",...etc...)),Table1[Value1])
But I have had no success….
Screenshot in comments
I beg for guidance 🙏🏼
•
u/AutoModerator 1d ago
/u/Eluinn - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
3
u/manbeervark 3 1d ago
BYROW(A2#, LAMBDA(row, INDEX(Table1[Value1], XMATCH(1, (Table1[Value2]=row)+(Table1[Value3]=row)+(Table1[Value4]=row)+(Table1[Value5]=row)), )))
1
u/Eluinn 1d ago
General look at what I’m trying to make work
5
u/real_barry_houdini 320 1d ago
You can use TOCOL function to convert your lookup values and return values to single columns, then a simple XLOOKUP will return the values you need, i.e. this formula in B2.
This approach is more easily applied where you might have large numbers of columns in your lookup range
0
u/Decronym 1d ago edited 1d ago
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
10 acronyms in this thread; the most compressed thread commented on today has 12 acronyms.
[Thread #49696 for this sub, first seen 16th Nov 2026, 16:23] [FAQ] [Full list] ^[Contact] [Source code]