CODEWITHSUNDEEP
excel
Divan
Divan

Reputation: 402

excel return row where data entry is matched

I want to compare the data of two columns in excel in a VLOOKUP manner. But, I want to return the row number of that match. Not just weather it is a match or not.

I want to return results like this, with A being my column to search, B being my data item to search for and column C being where my formula is:

 ------------------------
 |  A   |   B   |   C   |
 ------------------------
1|some  | data  |   2   |
2|data  | here  |   4   |
3|in    | some  |   1   |
4|here  | not   |  #N/A |

Upvotes: 1

Views: 132

Answers (1)

TVOHM
TVOHM

Reputation: 2742

To return the row number you can do:

=ROW($A$1) + (MATCH(B1,$A$1:$A$41,0) - 1)

You return the index of the first match in the array A1:A4. Then you subtract one from it and add it to the row number of the first element in the array to get the offset.

Upvotes: 2

Related Questions