Pull data from different sheet based on ID

Question

I have excel application, which contains 3 sheets (Sheet1, Sheet2 and Sheet3). Sheet 1 contains ID & Name columns and Sheet2 also have same ID & NAME, whereas Sheet1 will be static and Sheet2 is dynamic.

I want to fetch data from Sheet1 or Sheet2. When someone enter ID in sheet3 then i should pull the corresponding name from Sheet1 or Sheet2 and put it in sheet3.

I ma new to vba or excel formula. I dont know where i need to start.

Sheet1

+-----------+
| ID | Name |
+-----------+
| 1  | AAAA |
+-----------+
| 2  | BBBB |
+-----------+
| 3  | CCCC |
+-----------+

Sheet2

+-----------+
| ID | Name |
+-----------+
| 7  | SSSS |
+-----------+
| 9  | XXXX |
+-----------+

If I enter 3 in Sheet3, ID column then it should populate the CCCC in Name column.

Answer 1

Your question can be answered with a suggestion to use Vlookup function, but I would prefer VBA. So, please check the next code. It is a worksheet event, and must be copied in the sheet module (right click on the sheet name and choose View Code):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column = 1 Then
        Dim sh1 As Worksheet, sh2 As Worksheet, IDCell As Range
        Dim boolFound As Boolean
        Set sh1 = Worksheets("Sheet1"): Set sh2 = Worksheets("Sheet2")
        Set IDCell = sh1.Range("A:A").Find(What:=Target.Value, LookAt:=xlWhole)
        If Not IDCell Is Nothing Then
            If Target.Value <> "" Then
                Target.Offset(0, 1).Value = IDCell.Offset(0, 1).Value
                Target.Offset(0, 2).Value = IDCell.Offset(0, 2).Value: Exit Sub
            End If
        End If
        Set IDCell = sh2.Range("A:A").Find(What:=Target.Value, LookAt:=xlWhole)
        If Not IDCell Is Nothing Then
            If Target.Value <> "" Then
                Target.Offset(0, 1).Value = IDCell.Offset(0, 1).Value
                Target.Offset(0, 2).Value = IDCell.Offset(0, 2).Value: Exit Sub
            End If
        End If
        If Target.Value <> "" Then
            MsgBox """" & Target.Value & """ ID could not be found...": Target.Activate
        Else
            Target.Offset(0, 1).ClearContents
        End If
   End If
End Sub

It assumes that ID is in first column (A:A) of the three involved worksheet. If not, the code can be easily adapted...

Answer 2

I love VBA but for this one I think a VLOOKUP would be a lot more simple:

=IFERROR(VLOOKUP(A2,Sheet1!$A:$B,2,FALSE),VLOOKUP(A2,Sheet2!$A:$B,2,FALSE))

If you want to then add a 3rd,4th,5th column, just make sure the absolute references are correct, use COLUMN() instead of hard-coded number and fill it to the right too =IFERROR(VLOOKUP($A2,Sheet1!$A:$C,COLUMN(),FALSE),VLOOKUP($A2,Sheet2!$A:$C,COLUMN(),FALSE))

To do "error catching" wrap it in another IFERROR

=IFERROR(IFERROR(VLOOKUP($A2,Sheet1!$A:$C,COLUMN(),FALSE),VLOOKUP($A2,Sheet2!$A:$C,COLUMN(),FALSE)),"Id not found")

Answer 3

If you really want to use VBA so there's no formulae on the sheet, how about this?

To add another column to the lookup range just change the variable lastCol to whatever column you want!

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Dim lastCol As String: lastCol = "C"
        Range(Cells(Target.Row, "B"), Cells(Target.Row, lastCol)).Formula = _
        "=IFERROR(IFERROR(VLOOKUP(" & Target.Value2 & ",Sheet1!$A:$" & lastCol & ",COLUMN(),FALSE)," & _
        "VLOOKUP(" & Target.Value2 & ",Sheet2!$A:$" & lastCol & ",COLUMN(),FALSE)), ""Id not found"")"
        Range(Cells(Target.Row, "B"), Cells(Target.Row, lastCol)).Value2 = Range(Cells(Target.Row, "B"), Cells(Target.Row, lastCol)).Value2
    End If
End Sub