**1. VBA VLookUp – How to match rows?**

Do you want to match Excel records in VBA? VBA in Excel gives you the possibility to use the sheet functions. **VBA VLookup **is very important spreadsheet function. To use **vLookUp** function, refer to the worksheet functions using command **Application.WorksheetFunction.VLookup**. Let’s create two simple tables in Excel by the following pattern. Tables will be used in the example.

**Table 1:** Paste it into range **A1:B7**.

ID | VALUE |

1 | United States |

2 | England |

3 | Germany |

4 | Japan |

5 | Canada |

6 | Austria |

**Table 2:** Paste it into range **F1:G4**.

ID | VALUE |

1 | |

4 | |

6 |

Ok, now we need to fill G column using identifier listed in the Id Columns.

First of all, we should create a simple script that fills the cell “E2” with values searched using the function. In the Excel spreadsheet, we would insert the following formula into the cell “E2”: **=VLOOKUP(F2,A:B,2,FALSE)**. In the **VisualBasic** editor we need to insert a new module. The module should contain the following code using the sheet function:

1 2 3 4 |
'officeinside.org Sub vLookupExample1() Cells(2, 7) = Application.WorksheetFunction.VLookup(Cells(2, 6), Range("A:B"), 2, 0) End Sub |

**2. VBA VLookUp – How to drag the formula?**

Now the only thing we should do is loop our code to fill all the table values:

1 2 3 4 5 6 7 8 9 10 11 12 |
'officeinside.org Sub vLookupExample2() Dim lngCounter As Long lngCounter = 2 Do While lngCounter < Application.WorksheetFunction.CountA(Range("F:F")) + 1 Cells(lngCounter, 7) = Application.WorksheetFunction.VLookup(Cells(lngCounter, 6), Range("A:B"), 2, 0) lngCounter = lngCounter + 1 Loop End Sub |

Using the **CountA** sheet function, **we** checked how many non-empty values are in the “D” column. Loop will be repeated as many times as we have non-empty values in the “D” column. The filling starts with line no. 2, because we have to skip the table headers. In this way, our example using the VLookUp in VBA has been resolved. If you would like to serach values horizontal use the **hLookUp** function **similarly** .

If you are looking for similar examples of the practical programming in VBA, I invite you to visit this section: Excel VBA How To. Below the file with an example:

## 2 thoughts on “How to use VLookUp function in VBA Excel”

I must say got into this post. I found it to be interesting and loaded with unique points of interest.

This i like. Thanks!