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?