Menu
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
302
=MATCH() returns the position of a cell in a row or column. Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria.
The INDEX MATCH function is one of Excel's most powerful features. The older brother of the much-used VLOOKUP , INDEX MATCH allows you to look up values in a table based off of other rows and columns. And, unlike VLOOKUP , it can be used on rows, columns, or both at the same time.
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
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
