Clever Geek Handbook

Excel match value (get row) in two dimensional array

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 to use EXCEL ( non VBA ) functions to return a string of the corresponding string?

For example: given test

I need to return 4

givenertyui

6

I need:

  • the search will be performed in all columns of this two-dimensional array and

  • have function in only one cell

+3


source to share


3 answers


With your search string in A1:

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

Obviously, this won't work if there are multiple search strings in the range. However, each of the values ​​in the dataset you provide is unique, so I suppose this is not a problem.



Also, if that were possible, you would need to clarify which of the potential line numbers should be preferred.

Hello

+7


source


See examples with =MATCH() 

=match("test", E1:E7, 0)

This should return 4. Then change accordingly.



Better yet, put your condition in some other cell, say A1

, and then you can use:

=match($A$1, E$2:E$7, 0)-1

Place this formula in a cell E1

and drag across the columns and you will get a match for each column.

+1


source


Maybe with the desired string on line J1, in K1 (output) something like:

 =SUM(K2:K100)

and in K2 copied down to tell K100:

=IF(ISERROR(MATCH(J$1,E2:I2,0)),"",ROW())
+1


source






More articles:


All Articles