Formula to find match in two-dimensional range

Question

I need a formula that will look up a value in a 2-dimensional range and return the coordinates or cell address of the matching cell. For example:

R    A    B    C

1    John Matt Pete
2    Sara Bret Chad
3    Lila Maya Cami

I want to search the range A1:C3 for Chad and return C2 or 2,3. How can I accomplish this using Excel formulas? (I'll actually end up applying this to Google Sheets).

Thanks!

Phane

WHYT - what have you tried? Do you have something in mind? It would be great if you tell us about it. – Catgut

maybe writing a custom function in .gs might help. – Copier

Answer 1

Old question, but I thought I'd share a much simpler and elegant answer here that doesn't involve helper columns or complicated formulas, so that more people will get things done easier. Assuming that the table contains unique values and that you use E1 to store your search string Chad and E2 to display the result:

• if you want the row and column result of 2,3 in E2:

  =SUMPRODUCT((A1:C3=E1)*ROW(A1:C3)) & "," & SUMPRODUCT((A1:C3=E1)*COLUMN(A1:C3))
  

• if you want the R1C1 style cell address string of C2 in E2:

  =ADDRESS(SUMPRODUCT((A1:C3=E1)*ROW(A1:C3)),SUMPRODUCT((A1:C3=E1)*COLUMN(A1:C3)))
  

• if you want the found cell's contents of Chad in E2:

  =INDIRECT(ADDRESS(SUMPRODUCT((A1:C3=E1)*ROW(A1:C3)),SUMPRODUCT((A1:C3=E1)*COLUMN(A1:C3))))
  

How things work:

• SUMPRODUCT returns in this case the sum of the products between a boolean array of TRUE (searched value found in cell) and FALSE (searched value not found in cell) for every cell in the table and the corresponding row/column (absolute) numbers of those cells in the sheet; thus, the result is essentially the row/column (absolute) number of the cell where the value has been found, since TRUE=1 and FALSE=0 in mathematical terms
• ADDRESS returns a cell's address as text (not as reference!)
• INDIRECT returns the reference corresponding to a cell's text address

Source and credit goes to: this answer by XOR LX. Could have added the link in a comment, mentioning the duplicate question, but I wanted to expand and explain the answer a little bit, therefore more characters were needed.

Recompense

Thanks! Using this to look up where the value of a nearby cell (E2) occurs in a two-dimensional array (D2:H) in a separate sheet (sheet2), and then extract the value of the cell in the A column of the row where the value is found: =INDEX(sheet2!$A:$A, SUMPRODUCT((sheet2!$D$2:$H = E2) * row(sheet2!$D$2:$H))) – Orabelle

Answer 2

Assuming you're using Excel 2007 and above.

You will need a helper column. If your table looks like in your example, in cell D1 write:

=IFERROR(MATCH($E$1,$A1:$C1,0),0)

And drag it down. Then in cell E1 write your search value ("Chad" for instance). Then you have your search result in cell E2 with this formula:

=IF(MAX($D:$D)=0,NA(),MATCH(MAX($D:$D),$D:$D,1)&","&MAX($D:$D))

Cudweed

After re implementing this answer to my solution I had to fix the match to use match type 0, i.e. =IF(MAX($D:$D)=0,NA(),MATCH(MAX($D:$D),$D:$D,0)&","&MAX($D:$D)) above, except for that worked very nicely, thanks Taosique – Byssinosis

Answer 3

If you want a simpler solution, it is possible to use only one helper (or not at all, at the cost of a complicated formulae).

Let's say I take your example. I will use the D column to display result :

• In D1, I put the name I want to find : Chad
• In D2, I put the helper that will return an Index of the value searched (-1 if not found) : =IFERROR(MATCH(D1,SPLIT(TEXTJOIN(";",TRUE,A1:C3),";"),0),-1)
• In D3, I put the formulae to get the row,column value (FALSE if not found) : =IF(D2<>-1,ROUNDUP(DIVIDE(D2,COLUMNS(A1:C3))) & "," & IF(MOD(D2,COLUMNS(A1:C3))=0,COLUMNS(A1:C3),MOD(D2,COLUMNS(A1:C3))))

If you really want to use only one formulae, it is possible in D3 to replace all references to D2 by the formulae used in D2.

Cayenne

Answer 4

This formula returns the row and column number of a given value in a two-dimensional array.

=LET(
  array,           B2:D4,
  findvalues,      C7,

  arrayrows,       ROWS(array),
  arraycols,       COLUMNS(array),
  rowindex,        SEQUENCE(arrayrows*arraycols,,1,1/arraycols),
  colindex,        MOD(SEQUENCE(arrayrows*arraycols,,0),arraycols)+1,
  flatarray,       INDEX(array,rowindex,colindex),
  valueflatindex,  MATCH(findvalues,flatarray,0),
  valuerow,        ROUNDUP(valueflatindex/arraycols,0),
  valuecol,        MOD(valueflatindex-1,arraycols)+1,
  absvaluerow,     MIN(ROW(array))+valuerow-1,
  absvaluecol,     MIN(COLUMN(array))+valuecol-1,

  CHOOSE({1,2},absvaluerow,absvaluecol)
)

    A        B        C        D        E
1
2            John     Matt     Pete
3            Sara     Bret     Chad
4            Lila     Maya     Cami
5
6
7            find:    Chad
8            formula: 3        4

More precisely, this formula scans a given array row by row and returns the address of the first occurrence of a given value.

If you need the row and column numbers relative to the array's top left cell, then in CHOOSE(...), instead of absvaluerow/absvaluecol, use valuerow/valuecol.

If you want the values to be comma separated and in one cell, instead of CHOOSE(...),
use absvaluerow & "," & absvaluecol

If your Excel version does not support the latest functions, such as LET, the formula should still work if you rewrite it so that it does not use the LET variables.

Find Multiple Values

You can also find multiple values in an array using this formula as explained in my answer in this thread.

Feleciafeledy