Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Formula to find match in two-dimensional range

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!

like image 918
user3491852 Avatar asked Apr 03 '14 02:04

user3491852


2 Answers

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.

like image 137
Yin Cognyto Avatar answered Sep 23 '22 02:09

Yin Cognyto


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))
like image 23
ttaaoossuuuu Avatar answered Sep 24 '22 02:09

ttaaoossuuuu