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!

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.

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