excel match value(get row) in two-dimensional array

Question

I have the following structure in my excel sheet.

          E           F           G        H        
      ---------   ---------    ------    -------      
 2 |  blah        sometext     atext      smth
 3 |  textval     accc
 4 |  test        avfr
 5 |  check       
 6 |  ertyui      
 7 |  

Can you suggest a way using EXCEL functions (not VBA) of getting back the row of a matching string?

For example: given test I need to get back 4, given ertyui 6

I need:

• the lookup to be done in all the columns of this two-dimensional array and

• have the function in one cell only

Answer 1

With your search string in A1:

=SUMPRODUCT((E2:H6=A1)*ROW(E2:H6))

Obviously this will fail if there is more than one occurrence of the search string within the range. However, each of the values in the dataset you provide is unique, so I presume that this is not an issue.

Besides, if that was a possibility, you would need to clarify which of potentially several different row numbers should be the preferred return.

Regards