Lookup (or Search) for a value in one column across a range of columns, then return the corresponding value for a different column

[Hat4Life]

I have a data set that includes alternate SKU's for products. The alternate SKU's may be in any one of 31 different columns. I have a list of alternate SKU's that I want to look for i the range. When found, I want the formula to return the corresponding value in a different column. This will be the primary SKU.

Formulas I have tried: =XLOOKUP(J2,C:H,A:A,"")

=FILTER(C2:H13,ISNUMBER(SEARCH(J4,A:A)),"")

=INDEX($C$2:$H$13,MATCH(J7,$A$2:$A$13,0),7)

These do not produce the results needed.

In the attached image I am looking for the value in column J in any of the columns C through H. If found, I want the formula to return the corresponding value in column A.

 

[hagia_sofia]

How about this:

=FILTER($A$2:$A$13,BYROW(--($B$2:$E$13=G2),LAMBDA(a,SUM(a)))=1)

 

[Cubist]

The formula is to be inserted in 1 cell only. Clear out the cells below. Try:

=TOCOL(IFS(ISNUMBER(SEARCH(G2:G13,B2:E13)),A2:A13),2)

 

[Hat4Life]

How about this:

=FILTER($A$2:$A$13,BYROW(--($B$2:$E$13=G2),LAMBDA(a,SUM(a)))=1)

This worked. Thanks.

The screenshot I shared was a curated sample. The actual dataset is over 1800 SKU's that need to be found within 31 columns and 2400 rows. Not sure if it is the formula, or the amount of data evaluated, but each cell seems to take ~30 seconds to calculate. Locks up my computer if I run the formula on more than 10 cells at a time.

 

[Hat4Life]

The formula is to be inserted in 1 cell only. Clear out the cells below. Try:

=TOCOL(IFS(ISNUMBER(SEARCH(G2:G13,B2:E13)),A2:A13),2)

Tried it, but it would only return the correct value for the cell the formula in it. The next three cells would have the wrong result, and nothing populated after that.

 

[Cubist]

Tried it, but it would only return the correct value for the cell the formula in it. The next three cells would have the wrong result, and nothing populated after that.

There should be only 1 formula. I don’t understand what that means.

 

[hagia_sofia]

This worked. Thanks.

The screenshot I shared was a curated sample. The actual dataset is over 1800 SKU's that need to be found within 31 columns and 2400 rows. Not sure if it is the formula, or the amount of data evaluated, but each cell seems to take ~30 seconds to calculate. Locks up my computer if I run the formula on more than 10 cells at a time.

I just tested this formula on my computer:

=XLOOKUP(TRUE,ISNUMBER(BYROW($B$2:$E$13,LAMBDA(a,XMATCH(G2,a,0)))),$A$2:$A$13)

I did that testing on a randomized array of your size and even higher number of cases and Excel calculated it for about 10 seconds.