**“Constant effort and frequent mistakes are the stepping stones to genius” – Elbert Hubbard**

Contents

# A Quick Guide to the VBA VLookup

Parameters | Type |
---|---|

Lookup value | The value you are searching for |

Table array | The range you are searching through |

Column index | The column number of the value to return. |

Range look up | Optional - set to False for exact match only. |

# Introduction

The VLookup function can be a useful Excel function. Even though it is straightforward to use can often be confusing when used in VBA. In this post, I am going to show how anyone can easily use the VLookup function. I’ll also cover the pitfalls and how to avoid them. Of course, not post would be complete without a ton of examples that you can try for yourself.

If you are not familiar with VLookup in Excel then this page provides a great introduction.

**Notes:** I use the Underscore character(_) in the code examples. In VBA this allows you to split a line over multiple lines e.g.

' One line sResult = Application.VLookup("Apricot", Sheet1.Range("A10:B10000"), 1)

' Split up with underscore sResult = Application.VLookup( _ "Apricot", Sheet1.Range("A10:B10000"), 1)

# A Simple VBA VLookup example

Note: The variable *shData* in the examples refers to the worksheet by the code name. You can replace this with the code name of the worksheet you are using.

Take a look at the following data

The code below will return the price for the Orange **1.45**

Sub SimpleVLookup() Dim sRes As String sRes = Application.VLookup("Pear",shData.Range("A2:B7"),2) ' This will print 1.67 to the Immediate Window(Ctrl + G) Debug.Print sRes End Sub

The code looks for the text **Pear** in the range **A2:B7**. When it finds the text it returns a value from the same row as the text. The value in determined by the column number argument. We used **2** in this example.

Let’s look at some more examples and results

' Returns 1.45 sRes = Application.VLookup("Orange",shData.Range("A2:B7"),2) ' Returns 1.56 sRes = Application.VLookup("Apple",shData.Range("A2:B7"),2) ' Returns 1.22 sRes = Application.VLookup("Plum",shData.Range("A2:B7"),2) ' Returns Orange as column is 1 sRes = Application.VLookup("Orange",shData.Range("A2:B7"),1) ' Returns Apple as column is 1 sRes = Application.VLookup("Apple",shData.Range("A2:B7"),1) ' Returns Plum as column is 1 sRes = Application.VLookup("Plum",shData.Range("A2:B7"),1)

# The Parameters

In this section we will look at the four parameters. These are

**lookup_value**– The value to look up. It must be in the first column of the range.**table_array**– This is the range to search. This can also be a VBA array although it very slow using this.**col_index_num**– This contains the column number of the return value. Starts at column number one.**range_lookup**(optional) – Use True(default) to find closest match. Use False to find exact match. Using**True**assumes that the first columnis sorted alphabetically or numerically.

We will look at these parameters individually starting with the lookup_value parameter.

## Parameter 1: lookup_value

This is the value that you are looking up. It must be in the first column of the Range. If you are using the range C4:X10 then the lookup value must be in column C. If you are using the range Z1:AB5 then the lookup value must be in column Z.

The type of value you search for will normally be a string as this example shows

Sub StringVLookup() Dim sFruit As String sFruit = "Plum" Dim sRes As Variant sRes = Application.VLookup( _ sFruit, shData.Range("A2:B7"), 2, False) End Sub

We can also search for a number but you have to be careful here:

- If the number is stored as text then the search value must be a string.
- If the number is stored as a number then the search value must be a number.

For example in this data we have the lookup column stored as numbers

In this case, the lookup value must be a **Long** or you will get an error message.

Sub NumberVLookup() Dim num As Long num = 7 Dim sRes As Variant sRes = Application.VLookup( _ num, shData.Range("F2:G7"), 2, True) Debug.Print sRes End Sub

You can also use the **Double** data type if you are looking up a decimal value. As in the case of an integer it must be stored as a number if you want to use Double.

### Using VLookup on a Date Type

Using a **Date** type is a tricky business. VBA has a **Date** data type but the worksheet does not.

So the date type needs to be converted to a **Long** as the following examples show

theDate = CLng(#1/14/2017#) theDate = CLng(CDate("1/14/2017")) theDate = CLng(shData.Range("H10"))

You can then use it as normal in the VLookup function when the search column contains dates

Sub DateVLookup() Dim theDate As Long theDate = CLng(#1/14/2017#) Dim sRes As Variant sRes = Application.VLookup( _ theDate, shData.Range("I2:J7"), 2, False) Debug.Print sRes End Sub

## Parameter 2: table_array

This parameter refers to the range of the data we are looking up. You will normally use a range for this as we have seen in the examples so far.

If you are using a worksheet table you can use the range of the table.

Sub SimpleVLookupTable() Dim sRes As Variant ' Get the table Dim table As ListObject Set table = shData.ListObjects("Table1") ' Use the table for the table_array parameter sRes = Application.VLookup( _ "Plum", table.Range, 2, False) Debug.Print sRes End Sub

You can also use a VBA array with VLookup but this tends to be very slow.

## Parameter 3: col_index-num

This parameter refers to the column which contains the value you want to return. Column 1 is the leftmost column of the **table_array**.

If the column number is greater than the number of columns in the range you will get an error. See The VLookup Error Types section below.

## Parameter 4: range_lookup

This is an optional parameter. If it is not used then it takes **True** as the default value.

**False** means that an exact match must be found.

**True** means that an approximate match will be returned. The first column must be ordered numerically or alphabetically for this to work correctly.

Let’s look at the sample data again

The following code shows some examples of how this parameter works

Sub SimpleVLookup() Dim rg As Range Set rg = shData.Range("A2:B7") Dim sRes As Variant ' Stops at Orange - the last item before a P item sRes = Application.VLookup("P", rg, 2, True) ' Stops at Orange - the last item before a Pea item sRes = Application.VLookup("Pea", rg, 2, True) ' Stops at Peach - the last item before a Pead item sRes = Application.VLookup("Pead", rg, 2, True) ' Error - no exact match found sRes = Application.VLookup("Pea", rg, 2, False) End Sub

# Dealing with Errors

We can use VLookup in two ways in VBA. With **Application** or with **WorksheetFunction**

Application.WorksheetFunction.VLookup Application.VLookup

The difference between them is how we handle errors. Let’s look at each of these in turn.

## Using WorksheetFunction

Using **WorksheetFunction.VLookup** requires us to use **On Error** to trap the error. We can then check the error number **Err.Number** to see if the value is valid.

Sub UseWorksheetFunction() Dim sRes As Variant ' Turn on error trapping On Error Resume Next Err.Clear sRes = Application.WorksheetFunction.VLookup _ ("Plum", shData.Range("A2:B7"), 2, False) ' Check if value found If Err.Number = 0 Then Debug.Print "Found item. The value is " & sRes Else Debug.Print "Could not find value: " & "Plum" End If End Sub

## Using Application

Using **Application.VLookup** we can simply check the return value to see if there was an error

Sub UsingApplication() Dim sRes As Variant sRes = Application.VLookup _ ("Plum", shData.Range("A2:B7"), 2, False) ' Check if value found If IsError(sRes) = False Then Debug.Print "Found item. The value is " & sRes Else Debug.Print "Could not find value: " & "Plum" End If End Sub

## VLookup Error Types

The following table shows a list of the Excel cell error numbers and what they mean. These are the error numbers we get when we use **Application.VLookup**. This is taken from this MSDN Page

Constant | Error number | Cell error value |
---|---|---|

xlErrDiv0 | 2007 | #DIV/0 |

xlErrNA | 2042 | #N/A |

xlErrName | 2029 | #NAME? |

xlErrNull | 2000 | #NULL! |

xlErrNum | 2036 | #NUM! |

xlErrRef | 2023 | #REF! |

xlErrValue | 2015 | #VALUE! |

## Errors and Causes

The following table shows some common errors you may encounter with VLookup. If you’re having trouble with a particular VLookup error then it is a good idea to try it in Excel first.

Error | Cell | Possible causes |
---|---|---|

Error 2015 | #VALUE! | The column number is less than one. |

Error 2015 | #VALUE! | You used a string instead of a range for the table_array parameter. |

Error 2023 | #REF! | The column number is greater than the number of columns. |

Error 2042 | #N/A | The value is not found. See possible causes below. |

If you cannot find the value then check the following:

- Ensure the Table/Range is correct.
- Ensure the Table/Range does not include the header(VBA will think list is unsorted).
- Ensure the Table/Range is using the correct worksheet.
- If searching for a number use a long or double data type in the
**lookup_value**parameter. See lookup_value section above - If searching for a number stored as text use a string data type in the
**lookup_value**parameter. - If searching for a date convert it to a long(see Date Type above) in the
**lookup_value**parameter.

If you are getting the wrong value then check the following:

- If the
**range_lookup**parameter is True or not used, then ensure the first column is sorted alphabetically or numerically (see range_lookup above)

# VBA VLookup Speed

Sometimes you may need to perform a large number of lookups. In these cases, VLookup could be too slow. The VBA Dictionary is faster when dealing with a large number of lookups. The following code shows an example of using the Dictonary.

Sub UseDictionary() ' Get the range of values Dim rg As Range Set rg = shData.Range("M1:N20000") ' Create the dictionary Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") ' Fill the dictionary Dim cell As Range For Each cell In rg dict(cell.Value) = cell.Offset(0, 1).Value Next ' Perform the Lookups For Each cell In rg Debug.Print dict(cell.Value) Next End Sub

# What’s Next?

If you want to read about more VBA topics you can view a **complete list of my posts** here. I also have a free eBook(see below) which you will find useful if you are new to VBA.

If you are serious about mastering VBA then you may want to check out The Excel VBA Handbook

**Get the Free eBook**

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, **How to Ace the 21 Most Common Questions in VBA** which is full of examples you can use in your own code.

A masterpiece blog..

Thank you Paul, for writing this. What else to say!!

As you already know index match is much more efficient than vlookup which can further enhance the value of this topic. How about educating your audience in that area. In no way, I mean to under rate what you written for us.

Ravi.

Thanks Ravi.

Hi Paul,

first of all i like your explenations its great.

I had a question. how do i get de results on a field i want?

For example i want vba to return the results on sheet2 column b

kind regards

This depends on what exactly your results are. You write to a sheet using

`sheet2.Range("B2") = Vlookup()`

If you have multiple results then you need some kind of loop

Little comment to the part about the vlookup speed, if anybody would consider what solution to use when dealing with lots of data:

I have some data I get from the MRP system once a week. It contains over 70 000 rows (each for the single stock unit). One of things I need to do is pasting additional data in a column corresponding to those stock units).

So I checked three ways of doing this, after opening another file, from which I obtain this additional numbers:

1) using .FormulaR1C1 property to insert worksheet VLookup / or Index with Match function into the cells and quickly transforming them into values

2) using Application.Vlookup

3) using the dictionary like here

I used the Timer to measure the full time of those operations and there is only one winner:

1st method lasted 5 minutes and 34 seconds

2nd method – 4 minutes and 45 seconds

3rd method – 33 seconds.

Hi Tomek,

Thanks for those comparisons. They are interesting to read.

It’s another illustration of how memory is much faster for dealing with large amounts of data.

I cannot find a definition for shData… i.e. the macros including this does not work

shData is the worksheet variable or code name of the worksheet.

e.g.

Dim shData As Worksheet

Set shData = Worksheets(“Sheet1”)

Hi Kelly…I want to achieve VLOOKUP using dictionary, in getting this resolved I need your assistance.

Your immediate response is appreciated.

Thanks,

Surendra K.

As always, your article is very clear, but I’m still not sure where I should use vlookup over other methods? Are there any hidden upsides?

Hi Colin,

You normally use if the data is already on a worksheet and the format is suitable.

If you are going to be doing lots of lookups then a Dictionary is much faster.

Paul

shouldn’t these examples DIM shData? I’m guessing Dim shData as Range.

Hi John,

shDatarefers to the Code Nameof the worksheet. You can refer to the worksheet directly using the code name from the same workbook.

I will update the post as it is not clear.

Regards

Paul