Excel – How to get the row number from a vlookup

microsoft excelmicrosoft-excel-2010worksheet-function

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?

Best Answer

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)

Related Solutions

Excel – In Excel I need to find data matches between two arrays – one horizontal and one vertical

YMMV, but View -> Macros, add a macro. Try this (change the cell references as necessary):

Dim data, reference As Range

Set reference = Worksheets("Sheet2").Range("A1", "A42")
Set data = Worksheets("Sheet1").Range("B2", "F6")

For Each dataCell In data
    For Each referenceCell In reference
        If dataCell.Value = referenceCell.Value Then
                Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
        End If
    Next
Next

[Edit: If this works, you can speed it up a bit by stopping searching a row when you've found a match. (Assuming only one possible match per row). e.g.:

Sub newtest()
    Dim data, reference As Range
    Dim skipsome As Boolean
    skipsome = False

    Set reference = Worksheets("Sheet2").Range("A1", "A7")

    Set data = Worksheets("Sheet1").Range("B2", "F6")
    For Each dataCell In data
        For Each referenceCell In reference
            If dataCell.Value = referenceCell.Value Then
                    Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
                    skipsome = True
                    Exit For
            End If

            If skipsome = True Then
                skipsome = False
                Exit For
            End If
        Next
    Next
End Sub

Just on your 5 row test data this knocks the cell comparison tests down from 175 to 132.] [Edit2: Make the code work]

Excel – Get row number for the row matching multiple criteria in Excel

I would use the Power Query Add-In for this. It has a Merge command that can handle this requirement without writing a single formula or any code.

http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx?CTT=5&origin=HA103993872

I would start a Query based on your Application table. I would add an Index column (row number).

Then I would start another Query based on your Scheduled table. I would Merge that with the Application query, matching on Date and Code. Then I would use the Group By command to collapse by Date and Code and get the Min Index.

Best Answer

Related Solutions

Excel – In Excel I need to find data matches between two arrays – one horizontal and one vertical

Excel – Get row number for the row matching multiple criteria in Excel

Related Question