VBA Index & Match vs VLOOKUP

[mps80y]

Hi,

I'm in the process of writing some VBA to match and extract data from one worksheet to another.

Originally I was going down the path of VLOOKUP but have since read about Index & Match being the better way to go.

I've attached a sample of the data here, any suggestions on the best way to accomplish this would be greatly appreciated.

The headers on the 'invoice' tab match the headers for the data required from the 'data' tab, the corresponding columns are also stated in the square brackets. The lookup key is the 'Booking' column.

Any help or ideas are greatly appreciated.

Thanks in advance

 

[mps80y]

Depending on the software version of excel you are running, Power Query/Get and Transform may already be installed on your machine. Secondly, once you have run your merges and saved them, you can set the query to automatically refresh when new data is added to the source documents. While I understand that you will use Fluff's solution, I believe you are cheating yourself and your fellow workers of the latest Excel technology if you don't at least investigate the advantages of Power Query. It is an amazingly powerful addition to the Excel Suite of functions that may save you lots of work in the future. My 2 cents.

I agree - but sadly its an up hill battle with the powers that be.

 

[Fluff]

How about

Sub mps80y()
   Dim Cl As Range
   Dim wsData As Worksheet, wsInvoice As Worksheet
   Dim Ky As Variant
   
   Set wsData = Sheets("Data")
   Set wsInvoice = Sheets("Invoice")
   With CreateObject("scripting.dictionary")
      For Each Cl In wsData.Range("A2", wsData.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Application.Index(Cl.Resize(, 23).Value, 1, Array(8, 5, 6, 9, 10, 12, 15, 13, 2, 7))
      Next Cl
      For Each Cl In wsInvoice.Range("A2", wsInvoice.Range("A" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then
            Cl.Offset(, 11).Resize(, 8).Value = .Item(Cl.Value)
            Cl.Offset(, 1).Value = .Item(Cl.Value)(9)
            Cl.Offset(, 9).Value = .Item(Cl.Value)(10)
         Else
            For Each Ky In .Keys
               If Cl.Offset(, 2).Value = .Item(Ky)(9) Then
                  Cl.Offset(, 4) = Ky
                  Exit For
               End If
            Next Ky
         End If
      Next Cl
   End With
End Sub