title: Macros for Vlookup using Column Header
body: <p>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. </p>
  
  <p>This is Sheet 1. I will enter the 'Member ID' Then do Vlookup to sheet 2 to get 'Member Name' &amp; 'Remarks'.</p>
  
  <p><img src="https://i.stack.imgur.com/uxiuu.png" alt="enter image description here"></p>
  
  <p>This is Sheet 2, where all the data are stored.</p>
  
  <p><img src="https://i.stack.imgur.com/Lq0kt.png" alt="enter image description here"></p>
  
  <p>This is the Vlookup codes.</p>
  
  <pre><code>With Sheets("Sheet1")
  .Range("G2:G" &amp; .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" &amp; .Cells(.Rows.Count, "F").End(xlUp).Row).Formula = "=VLOOKUP(RC[-2],Sheet2!C[-4]:C,5,FALSE)"
  End With
  </code></pre>
  
  <p>I trying to Vlookup by searching for the Column header. But I not sure how to I do it.</p>
  
  <pre><code>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
  </code></pre>