# How can I get the row number from a vlookup?

So I have two spreadsheets where there is some messy data in one of them that needs to be in another.

What I have done so far is copy the columns I want from the messy spreadsheet into a new tab on the clean one. On the clean one, one of the columns is being used as a unique key, but on the messy one, some of those values are duplicated in multiple rows with different information (different columns though, so not overlapping) that I want to pull into the clean one.

I was going to use a formula like this:

``````=IF(NOT(ISNA(VLOOKUP(E2,Sheet2!A:A,1,FALSE))),Sheet2!B1)
``````

But then I realized that VLOOKUP doesn't return the row number where it found that value. (Sheet2 is the tab with the data from the messy spreadsheet. Column B on that spreadsheet is the data I want, but the row should be where it was found instead of an explicit number value like that.)

Is there a function that will return the row where Excel found that value?

I believe that you want "match."

``````=MATCH(E2,Sheet2!A:A,0)
``````

If you want to return the value from that cell, you can use the following:

``````=INDEX(B:B,MATCH(E2,Sheet2!A:A,0))
``````

But, to be clear, the above is only for illustration, as you should use the following instead in that particular circumstance:

``````=VLOOKUP(E2,Sheet2!A:B,2,FALSE)
``````
• Thanks. How do I turn that match into the cell value? In other words, how can I get something like (pseudo-code) `cell(column(B), row(MATCH(E2,Sheet2!A:A,0)))`? Aug 29, 2013 at 17:46
• Man, you were so close: `=INDEX(B:B,MATCH(E2,Sheet2!A:A,0))`
– Dane
Aug 29, 2013 at 18:04
• Lol, I'm admittedly an Excel newbie. I could see in my head how to do this in code, but creating a VBA macro would have been overkill. Aug 29, 2013 at 18:33