I've been looking high and low and can't find out a solution. It seems like i get close and then hit a wall.

I have a "Sheet 1" like this:

There is a "Sheet2" with this:

What I am trying to do is a lookup from column A (Sheet2) that will search Columns B:F in Sheet1 and return column A in sheet1 in column B of sheet 2.

I tried search reverse vlookup index match.. can't quite figure it out.

Any help would be appreciated

I've tried vlookups, but it is not quite what I expected. I got lost when trying to index.

CodePudding user response：

Use INDEX/AGGREGATE:

=INDEX(A:A,AGGREGATE(15,7,ROW($B$1:$F$4)/($B$1:$F$4=I1),1))

For Office 365 we can use FILTER:

=@FILTER($A$1:$A$4,BYROW($B$1:$F$4,LAMBDA(a,ISNUMBER(MATCH(I1,a,0)))))

CodePudding user response：

Lookup In Multiple Columns

In Microsoft 365, you could use this spilling formula:

=LET(sv,Sheet1!A1:A4,sl,Sheet1!B1:F4,dl,A1:A5,
    sc,COLUMNS(sl),sr,INT((XMATCH(dl,TOCOL(sl))-1)/sc 1),
        INDEX(sv,sr))

Formulas

D1    =TOCOL(Sheet1!B1:F4)  
E1    =XMATCH(A1:A5,D1#)    
F1    =INT((E1#-1)/COLUMNS(Sheet1!B1:F4) 1) 
G1    =INDEX(Sheet1!A1:A4,F1#)  

Substitution

F1#   =INDEX(Sheet1!A1:A4,INT((E1#-1)/COLUMNS(Sheet1!B1:F4) 1)) 
E1#   =INDEX(Sheet1!A1:A4,INT((XMATCH(A1:A5,D1#)-1)/COLUMNS(Sheet1!B1:F4) 1))   
H1 (D1#) 
=INDEX(Sheet1!A1:A4,INT((XMATCH(A1:A5,TOCOL(Sheet1!B1:F4))-1)/COLUMNS(Sheet1!B1:F4) 1)) 

LET

B1    =LET(sv,Sheet1!A1:A4,sl,Sheet1!B1:F4,dl,A1:A5,
              sc,COLUMNS(sl),sr,INT((XMATCH(dl,TOCOL(sl))-1)/sc 1),
                  INDEX(sv,sr)) 

sv    Source Value Range
sl    Source Lookup Range
dl    Destination Lookup Range
sc    Source Columns Count
sr    Source Row