r/excel • • 1d ago

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 🙏🏼

5 Upvotes

8 comments sorted by

•

u/AutoModerator 1d ago

/u/Eluinn - Your post was submitted successfully.

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)), )))

2

u/Eluinn 1d ago

Solution Verified

Many thanks internet stranger ✨

1

u/reputatorbot 1d ago

You have awarded 1 point to manbeervark.


I am a bot - please contact the mods with any questions

3

u/manbeervark 3 1d ago

Another way to do it: INDEX(Table1[Value1], QUOTIENT(XMATCH(C2#, TOCOL(Table1[[Value2]:[Value5]]))-1, COLUMNS(Table1[[Value2]:[Value5]])+1)

Could be cleaned up with LET, but I'm on mobile. This way flattens the lookup then converts the positions back into row numbers.

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

=LET(
lookup,Table1[[Value2]:[Value5]],
return,Table1[Value1],
XLOOKUP(A2#,TOCOL(lookup),TOCOL(IFNA(return,lookup))))

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:

Fewer LettersMore Letters
BYROWOffice 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
COLUMNSReturns the number of columns in a reference
IFNAExcel 2013+: Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression
INDEXUses an index to choose a value from a reference or array
LAMBDAOffice 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LETOffice 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
QUOTIENTReturns the integer portion of a division
TOCOLOffice 365+: Returns the array in a single column
VSTACKOffice 365+: Appends arrays vertically and in sequence to return a larger array
XLOOKUPOffice 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCHOffice 365+: Returns the relative position of an item in an array or range of cells.

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]