How to do VLOOKUP?

Macro for Vlookup Tutorial

A Vlookup function search for ‘value’ in the first column of ‘table Range’ & returns a value from the same row.

Syntax:

=Vlookup (‘value to search’, ‘table range’, ‘column number to return value’, ‘match type’ – Exact or approximate match)

This article explains how to use this function manually within Excel cell & how to automate Vlookup with VBA Macro code.

How to Code a Vlookup Macro?

A Vlookup in VBA macro can be coded 2 possible methods as explained here.

1.Vlookup in Cell using Macro

With this trick, you can insert VLOOKUP formula into a Cell through VBA Macro during run-time. Use Range.Formula or Activecell.Formula to assign any formula to Cell or Range as given below.

ActiveCell.Formula = "=VLOOKUP(A3,A1:E4,5,FALSE)"

There is also another variant of this function. In case if you want only the value of this formula, then use Evaluate which will not insert formula, but it will calculate the value and insert value into the cell.

ActiveCell.Formula = Evaluate("=VLOOKUP(A3,A1:E4,5,FALSE)")

Related: How to remove #N/A #Value Errors in Excel Formula?

2. Use Vlookup in VBA Macro to Lookup a Cell Value:

Not only a LOOKUP function, there are many excel formula that can be used with the help of WorkSheetFunction Collection as given below.

Sub VLOOKUP_In_Macro()
    'Function with Value as Input
    MsgBox WorksheetFunction.VLookup("Sachin", ThisWorkbook.Sheets(1).Range("A1:E4"), 5, False)
    'Function with Cell value as Input
    MsgBox WorksheetFunction.VLookup(ThisWorkbook.Sheets(1).Cells(2, 1), ThisWorkbook.Sheets(1).Range("A1:E4"), 5, False)
End Sub

Adjust the Vlookup function to include the column that you want to search to be the First column.

Why Vlookup not Working for You?

Many people report that vlookup is not working, mainly because they don’t understand that this function will search only the first column of input table, but not in whole table.

i.e., the value that you want to lookup has to be in column 1 of the input table range or array, otherwise the function will not return a desired value.

How a Vlookup Works?

Consider a table has values as given below. A traditional student database like table with their scores in different subjects is given below.

Now the task is to get lookup for anyone’s score2 that is in column 3. The function below will be able to fetch this result.

=VLOOKUP(“Nolan”,A2:E4,3,FALSE) – Get Score2 that is in Column 3 for “Nolan”. This will return value 30.

Here the lookup is done on first column. So a VLOOKUP function is used. In case if this has to be done on a row, use HLOOKUP function or LOOKUP function which also work similar.

This covers all the basics about VLOOKUP function. This is one of the most useful function when someone has to deal with data in the form of tables. So, it is better to refresh some basics often before using this function.