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:


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?

1 Answer 1


I believe that you want "match."


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


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

  • 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
  • 1
    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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .