CODEWITHSUNDEEP
excelvlookupvba
Gervina
Gervina

Reputation: 63

Macros for Vlookup using Column Header

I have 2 Worksheets. I will have a macro to do the Vlookup in Sheet1. Sheet2 will be the data stored. However the column of the data won't be same for each term, therefore I will have to check the column name.

This is Sheet 1. I will enter the 'Member ID' Then do Vlookup to sheet 2 to get 'Member Name' & 'Remarks'.

enter image description here

This is Sheet 2, where all the data are stored.

enter image description here

This is the Vlookup codes.

With Sheets("Sheet1")
.Range("G2:G" & .Cells(.Rows.Count, "F").End(xlUp).Row).Formula = "=VLOOKUP(RC[-1],Sheet2!C[-3]:C[-2],2,FALSE)"
End With
With Sheets("Sheet1")
.Range("H2:H" & .Cells(.Rows.Count, "F").End(xlUp).Row).Formula = "=VLOOKUP(RC[-2],Sheet2!C[-4]:C,5,FALSE)"
End With

I trying to Vlookup by searching for the Column header. But I not sure how to I do it.

Worksheets("Sheet2").Activate
' Map Account name
Rows("1:1").Select
Set cell = Selection.Find(What:="Member ID", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If cell Is Nothing Then
    MsgBox "Column 'Member ID' could not be found.", vbCritical, "No such column"
    Cancel = True
Else
    'Vlookup here

Upvotes: 1

Views: 3998

Answers (1)

Peter Albert
Peter Albert

Reputation: 17475

No need for VBA here! you can do this with formula all along. Two options:

1. Use INDEX/MATCH

Use the following formula in G2: =INDEX(Sheet2!E:E,MATCH(F2,Sheet2!D:D,0). Replace the E:Ewith H:H in the next column.

you can now shift the order of the columns ins sheet 2 to your liking.

2. Use VLOOKUP and MATCH

In case you don't know the order up front, you can also just find the column number first - and then build the VLOOKUP:

  • In Sheet1, add a row above the header. Place the following formula in F1 (and copy to H1): =MATCH(F2,Sheet2!1:1,0). This will return the column number for each header.
  • Now place the following formula in G3 and copy it down and right to column H: =VLOOKUP($F3,Sheet2!$D:$Z,G$1-F$1+1,0)

Hope that help!

Upvotes: 1

Related Questions