“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, no 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:
Use this code to generate this data on any worksheet:
' Use this sub to generate the data ' https://excelmacromastery.com Sub GenerateData() ' Change the sheet name as required With ThisWorkbook.Worksheets("Sheet1") .Range("A1").CurrentRegion.ClearContents .Range("A1:A7").Value = WorksheetFunction.Transpose(Array("Fruit", "Apple", "Apricot", "Orange", "Peach", "Pair", "Plum")) .Range("B1:B7").Value = WorksheetFunction.Transpose(Array("Price", 1.56, 2.33, 1.45, 2.28, 1.67, 1.22)) End With End Sub
The code below will return the price for the Pear i.e. 1.45
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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.
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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.
' https://excelmacromastery.com/ 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:
' https://excelmacromastery.com/ 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.
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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 Dictionary.
' https://excelmacromastery.com/ 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?
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars and all the tutorials.
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
hi sir paul can i or is there vlookup to the left range
thank you for your attention and answers
rian
Dear Paul Kelly.
My name is Frank, I live in Italy.
I would like to send you this question to have your answer from you, if possible.
Using the “Vlookup” function and a VBA code in a module, in the current spreadsheet I am trying to return in a range of cells the corresponding value imported from a second spreadsheet, including any related comment with its original formatting.
The Vlookup formula is as follows: =VlookupComment (H2; A2:C10;3;FALSE)
And the VBA code in the module is the follows:
Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
‘Frank [12.03.20]
Application.Volatile
Dim xRet As Variant ‘potrebbe risultare un errore
Dim xCell As Range
xRet = Application.Match(LookVal, FTable.Columns(1), FType)
If IsError(xRet) Then
VlookupComment = “value not available”
Else
Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
VlookupComment = xCell.Value
With Application.Caller
If Not .Comment Is Nothing Then
.Comment.Delete
End If
If Not xCell.Comment Is Nothing Then
.AddComment xCell.Comment.Text
End If
End With
End If
End Function
Everything is working, however the comment text is not imported with the same original formatting but in a small box and with different characters.
Can You help me to solve this problem?
Thank you very much …
I await your kind reply.
Frank, Italy
Sheet1.Cells(input_row, name_clm) = Application.VLookup(Sheet1.Cells(input_row, name_clm), Sheet2.Range(“C2: C7”), 1, 1)
this code run good
I want to use “cells” format instead of “C2:C7”
.Range(Cells(1, name_clm), Cells(last_row, name_clm))
but it doesn’t work! why?
Put full stop before Cells. e.g. .Range(.Cells
‘ActiveCell.FormulaR1C1 = “=VLOOKUP(RC[-1],Data!C[1]:C[2],2,0)”
‘ActiveCell.FormulaR1C1 = “=VLOOKUP(RC[-2],Data!C:C[2],3,0)”
For this formula instead of activecell… i want it to be C2 for the first line and D2 for the second line…how do i amend this code
See Ranges and Cells
Hi Paul,
Really great stuff, thank you.
Just to note a small mistake in VBA VLookup – A Complete Guide. You write “The code below will return the price for the Orange 1.45” while the code actually looks for the price of the Pear 🙂
Best
Hi Paul,
I’m trying to use vlookup to get a value from another workbook but I get an error on the “set vendas line” Error = Run-time error 9: Subscript out of range.
Sub vlookup()
Dim tabelatorno As Range
Dim ultimalinha As Long
Dim vendas As Workbook
ultimalinha = Folha1.Range(“A1”).CurrentRegion.Rows.Count
Set tabelatorno = Folha1.Range(Folha1.Cells(2, 1), Folha1.Cells(ultimalinha, 7))
Set vendas = Workbooks(“F:\Publico\GESTÃO\vendas.xlsm”).Worksheets(Folha1.Range(“B2”).CurrentRegion)
tabelatorno.Columns(7).Value = WorksheetFunction.vlookup(tabelatorno.Columns(1).Value, vendas, 11, 0)
End Sub
Can you help me out to figure it out?
Thanks,
Luís
Dear Paul Kelly
Always thanks your VBA, it is great helpful to me.
in order to speed up, I am interesting Dict. vlookup.
but I’d faced problem. @ “number stored as Text”
0123 (format 0000#)
Dict.key saved as double. So, I cannot get matched result.
(due to type different?, double and string.)
do you have idea to resolve this issue?
Thanks so much.
How can I print 0 instead of the #N/A error returned by the vertical formula written in vba. I look forward to your help on this.
Sub getir_8()
isim = Worksheets(“database”).Range(“D187:D1819”).Value
duseyara = Application.WorksheetFunction.VLookup(isim, Worksheets(“2022_DBS”).Range(“D2:P954”), 13, 0)
Worksheets(“database”).Range(“N187:N1819”).Value = duseyara
MsgBox (“işlem Tamam”)
End Sub
“”The macro is working correctly. The problem is “#N/A” Let it return “0”. I’m asking for a solution on this issue.
“I can’t do math with #N/A.”
Thanks.