CODEWITHSUNDEEP
excelsearchexcel-formulaexcel-2016lookup-tables
elizac
elizac

Reputation: 11

MATCH single cell in multiple columns for INDEX

I know this may seem simple, but I'm going insane at this point.

Say I have the value 123456 (a cell in a sheet), and I want to search for it in the table below in another sheet and return the corresponding A column value:

   A      B      C      D
1  651655 231232 543653 453242
2  561632 151241 123456 453425
3  156321 534254 453455 445364

I have tried:

=INDEX(A:A,MATCH(123456,B:D,0))
=INDEX(A:A,MATCH(123456,B:B & C:C & D:D,0))

But alas, I only return N/A. I have over 1000 "123456"s I need to search and assign corresponding values, so doing it by hand is not an option

When I do: =INDEX(A:A,MATCH(123456,C:C,0)) it works, but I need to be able to check all columns.

Upvotes: 0

Views: 109

Answers (2)

Black cat
Black cat

Reputation: 6097

The reason of the behaviour of the MATCH function is, that since it returns the relative position of the found item, the lookup array must be a single column or row.

To resolve this apply one of the following formulas: (presume the range is:A1:D15 and the value to look for is 10.

  • If only unique values are in the lookup table: =MAX((B1:D15=10)*A1:A15)
  • If there are identical values in: =INDEX(A1:A15,SMALL(IF(IF(B1:D15=10,1,0)=0,ROWS(A1:A15)+1,ROW(A1:A15)),1),1)

With this formula the n-th item can be found if instead x place the value of it. =INDEX(A1:A15,SMALL(IF(IF(B1:D15=10,1,0)=0,ROWS(A1:A15)+1,ROW(A1:A15)),x),1)

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36750

If they are always number value then could try SUMPRODUCT().

=SUMPRODUCT(A1:A3*(B1:D3=G2))

For number or text could try-

=INDEX(A1:A3,MAX(--MAP(B1:D3,LAMBDA(x,x=G2))*ROW(B1:D3)))

enter image description here

Upvotes: 2

Related Questions