8

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?

Improve this question

1 Answer 1

Reset to default
11

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)
Improve this answer
3
  • 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)))?
    – supercheetah
    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.
    – supercheetah
    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 .