CODEWITHSUNDEEP
excel
Rujj
Rujj

Reputation: 57

compare specific columns from sheet 1 with sheet 2 and return value of matched row in sheet 2

enter image description here

sheet 1

enter image description here

sheet 3

I want to compare state , district , pincode from sheet1 with sheet3 and return Id(column A) in sheet 3 to pincode_ref_id(column C) in sheet 1.

Can any one help in this, i have checked with vlookup but could not make it. Thank you in advance.

Upvotes: 2

Views: 968

Answers (2)

Adisak Anusornsrirung
Adisak Anusornsrirung

Reputation: 690

I suggest you to use macro. Put the code below in module in Visual Basic Editor then run it.

Sub matchId()
Dim i As Long
Dim j As Long


i = 0
j = 0
Do While Sheets("Sheet1").Range("D2").Offset(i).Value <> ""
    Do While Sheets("Sheet3").Range("C2").Offset(j).Value <> ""
        If Sheets("Sheet1").Range("D2").Offset(i).Value = Sheets("Sheet3").Range("C2").Offset(j).Value And _
            Sheets("Sheet1").Range("E2").Offset(i).Value = Sheets("Sheet3").Range("D2").Offset(j).Value And _
            Sheets("Sheet1").Range("F2").Offset(i).Value = Sheets("Sheet3").Range("E2").Offset(j).Value Then

            Sheets("Sheet1").Range("C2").Offset(i).Value = Sheets("Sheet3").Range("B2").Offset(j).Value
        End If
        j = j + 1
    Loop
    i = i + 1
    j = 0
Loop
End Sub

Go to Visual Basic Editor by press Alt+F11
From top menu select Insert -> Modules to add modules
Double click on Module1 in project windows(left hand side).
Copy and paste the code and run by press F5 key or select Run -> Start from top menu.

Or follow this page for how to create VBA macro. http://www.exceldigest.com/myblog/2012/07/01/how-to-write-a-vba-macro-in-excel-2010/

Upvotes: 1

Balinti
Balinti

Reputation: 1534

For the 2nd row enter:

=INDEX(Sheet3!A:A,MATCH(Sheet1!D2&E2&F2,Sheet3!C:C&Sheet3!D:D&Sheet3!E:E,0))

The difference between lookup and index with match is that you can run this on unlimited matches and it is not have to be the left most column.

Note that this is an array formula and you have to enter it with ctrl+shift+enter

Upvotes: 1

Related Questions