VBA VLookup – A Complete Guide

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

Contents

1 A Quick Guide to the VBA VLookup
2 Introduction
3 A Simple VBA VLookup example
4 The Parameters
5 Dealing with Errors
6 VBA VLookup Speed
7 What’s Next?
8 Get the Free eBook

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

' 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.

© BigStockPhoto.com

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

© BigStockPhoto.com

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.)

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.

VBA Vlookup VBA Worksheet Functions

54 comments

Ravi Agarwal says:

June 3, 2016 at 11:07 pm

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.

Reply
1. Paul Kelly says:
  
  June 4, 2016 at 8:39 am
  
  Thanks Ravi.
  
  Reply
achietje says:

July 15, 2016 at 9:36 am

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

Reply
1. Paul Kelly says:
  
  July 15, 2016 at 12:47 pm
  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
  Reply
  1. Divya says:
    
    June 7, 2017 at 8:42 am
    
    Hello Paul,
    How can we give 2 lookup values and get the result in single cell. Kindly help me on this.
    Thanks.
    
    Reply
    1. Paul Kelly says:
      
      June 7, 2017 at 10:05 am
      
      The best way is to create a column that combines the two values.
      
      Reply
  2. Peggy Wong says:
    
    November 19, 2019 at 7:22 am
    
    Dear Pual
    
    ok, just now some things came out from my mind. if I am going to do a vlookup using vba excel.
    1. call up vendor name > link to they are due with which industry. How am i going to run the code?
    
    Reply
tomek says:

September 27, 2016 at 11:09 am

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.

Reply
1. Paul Kelly says:
  
  September 27, 2016 at 8:08 pm
  
  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.
  
  Reply
LM says:

October 12, 2016 at 8:31 am

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

Reply
1. Paul Kelly says:
  
  October 12, 2016 at 8:48 am
  
  shData is the worksheet variable or code name of the worksheet.
  
  e.g.
  Dim shData As Worksheet
  Set shData = Worksheets(“Sheet1”)
  
  Reply
Surendra K says:

January 5, 2017 at 6:36 pm

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.

Reply
Colin says:

January 26, 2017 at 5:05 pm

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?

Reply
1. Paul Kelly says:
  
  January 27, 2017 at 9:18 am
  
  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
  
  Reply
John Desselle says:

April 27, 2017 at 3:43 pm

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

Reply
1. Paul Kelly says:
  
  April 28, 2017 at 3:40 am
  
  Hi John,
  
  shData refers to the Code Name
  of 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
  
  Reply
Denis Irish says:

June 6, 2017 at 11:49 am

Hi Paul,

If there is a duplicate in column A (A6=Pears and A8=Pears), how do I get the result for the second entry?
I am using a user form with a text entry to look up data.

Private Sub CommandButton1_Click()
On Error GoTo MyErrorHandler:

Dim name As String
name = TextBox1.Text
sal = Application.WorksheetFunction.VLookup(name, Sheet1.Range(“A2:D5”), 4, False)
MsgBox “Salary is ” & sal

MyErrorHandler:
If Err.Number = 1004 Then
MsgBox “Does Not Exist”
End If
End Sub

Reply
1. Paul Kelly says:
  
  June 7, 2017 at 10:03 am
  
  Hi Denis,
  
  VLookup expects the lookup value to be unique. If you can make them unique e.g. using firstname and surname it is the best solution.
  
  You can also use Find. This can be slow if there are a lot of items.
  
  Paul
  
  Reply
Naveen says:

August 30, 2017 at 11:16 am

Hi Paul,
I am trying the vlookup with two textboxes how do I get the same instead of msg box..

Reply
1. Paul Kelly says:
  
  August 31, 2017 at 4:54 am
  
  I don’t understand your question.
  
  Reply
Naveen says:

September 1, 2017 at 2:34 am

I have two textbox in my userform, when I enter the name in first textbox I want to perform the vlookup function with value and get the lookup value in textbox 2.

Reply
1. Nurtac says:
  
  September 15, 2019 at 3:46 pm
  
  Hi,
  You can use this code in vlookup
  fdata = textbox1 & ” ” & textbox2
  
  Reply
Kashif says:

September 18, 2017 at 5:51 pm

Hi Paul Thanks for this lovely post, however I am facing little problem here, below code is working fine, if the data in the variable “mydata2” is less than or equal to 68 thousands if the data is above this like you 80000, it is give an error message “Type Mismatch” in the line “Range(“B2″).Resize(UBound(mydata3), 1).Value = Application.Transpose(mydata3)”

Sub UseDictionary()

‘ Get the range of values
Dim rg As Range
Dim mydata As Variant
Dim mydata2 As Variant
Dim mydata3() As Variant

Dim mytime As Date

mytime = Now

mydata = Sheet1.Range(“A2:B500001”).Value
mydata2 = Sheet2.Range(“A2:a80000”)

‘ Create the dictionary
Dim dict As Object
Set dict = CreateObject(“Scripting.Dictionary”)

For i = LBound(mydata, 1) To UBound(mydata, 1)
dict(mydata(i, 1)) = mydata(i, 2)
Next i

ReDim mydata3(1 To UBound(mydata2))

For i = LBound(mydata2, 1) To UBound(mydata2, 1)
mydata3(i) = dict(mydata2(i, 1))
Next i

Range(“B2”).Resize(UBound(mydata3), 1).Value = Application.Transpose(mydata3)

MsgBox Format(Now – mytime, “hh:mm:ss”)

End Sub

Please help me where I am doing wrong

Reply
1. Paul Kelly says:
  
  September 19, 2017 at 8:47 am
  
  It’s not possible to find the problem without seeing the data you are using.
  
  Put a breakpoint on the line with the error. Place the different parts in the Watch window to find which part is causing the error.
  
  Reply
Amir says:

January 2, 2018 at 5:42 am

dear Paul,

i want to do vlookup between two sheets. in second sheet i have two columns.
could you please guide me?

Reply
RaviKumar Dayalan says:

February 26, 2018 at 3:46 pm

Hi Paul,

how to use dictionary method for Vlookup approximate match?

Reply
1. Paul Kelly says:
  
  March 2, 2018 at 6:06 am
  
  You can use the Like operator.
  
  Reply
Zazy says:

April 10, 2018 at 6:40 am

The VBA is throwing a run time error instead of continuing to loop through my range.
How to ignore it and continue / How to modify the code below to be using Application.Vlookup:

Sub Get_Price()

Dim LoopCount As Integer
Dim OutOffset As Integer
Dim POSHIPMENT As Workbook

LoopCount = 2
OutOffset = 2
Set POSHIPMENT = Workbooks.Open(“D:\Data\Desktop\po shipments.xlsx”, True, True)

While Worksheets(“FNDWRR”).Range(“B” & LoopCount).Value vbNullString

Worksheets(“FNDWRR”).Range(“I” & OutOffset).Value = Application.WorksheetFunction.VLookup(Worksheets(“FNDWRR”).Range(“B” & OutOffset) & “-” & Worksheets(“FNDWRR”).Range(“C” & OutOffset), POSHIPMENT.Worksheets(“PO Shpmnts”).Range(“A:H”), 8, “FALSE”) * Worksheets(“FNDWRR”).Range(“H” & OutOffset).Value
LoopCount = LoopCount + 1
OutOffset = OutOffset + 1

Wend
End Sub

Reply
1. Paul Kelly says:
  
  April 10, 2018 at 2:04 pm
  
  Hi Zazy.
  
  The following section shows how to use Application.VLookup and WorksheetFunction.VLookup when dealing with errors : Dealing with Errors in VLookup.
  
  Reply
SVStew says:

May 3, 2018 at 11:45 am

Hi Paul,

I have made a spreadsheet to calculate the landed cost of wines we import. What I am trying to do is to make a submit button once the calculation is done to take the landed cost and put it next to the corresponding wine on worksheet 2. I have got all of our wines (roughly 200) on the 2nd worksheet and a form on the 1st worksheet which can be filled in with up to 20 wines which can be selected using a Dropdown list. On the 1st sheet, I have used the Match function in column K to return which row the wine appears on in worksheet 2, so using some of your examples, I have been trying to adapt it to work for me. This is the code;

Private Sub CommandButton2_Click()

Dim i As Long
Dim x As Integer
For i = 2 To 21

If Range(“M” & i) > 1 Then
x = Worksheets(1).Range(“K”, i).Value
Worksheets(2).Cells(x, 12).Value = Worksheets(1).Range(“I” & i).Value
End If

Next
End Sub

It keeps returning a Run-time Error ’13’: and when i click Debug, it highlights the ‘x = Worksheets(1).Range(“K”, i).Value’
Your assistance would be greatly appreciated
Kind regards

Reply
1. Paul Kelly says:
  
  May 3, 2018 at 1:51 pm
  
  Hi Stewart,
  
  You are getting a Type Mismatch.
  
  The variable x is an integer and I am guessing that the value of Worksheets(1).Range(“I” & i).Value is text.
  
  Place the cursor over value and the actual value of the cell should appear.
  
  Reply
nivagnimda says:

May 10, 2018 at 4:01 pm

Hi, I’m using the method you detailed to lookup a form input [TxtRef] and then perform the Vlookup upon changing that data. And the code runs, but does not output, if I allow errors, the lookup returns Error 2042 (#N/A). I’ve checked the vlookup in a cell on a blank sheet and I get the data back, but when I add some captions to debug, the variable [table] seems to be populated with “table1” rather than the range contents… So I wonder if this is why it fails. If you have any idea, it would be greatly appreciated. (WorkList) is both the sheetname and codename)

Private Sub TxtRef_AfterUpdate()

Dim LookupVal As Variant
Dim table As ListObject
Set table = WorkList.ListObjects(“Table1”)

LookupVal = Application.VLookup(TxtRef, table.Range, 13, False)
Me.Output1.Caption = TxtRef
Me.output2.Caption = table
Me.Stat.Caption = LookupVal

End Sub

Reply
1. Paul Kelly says:
  
  May 11, 2018 at 9:35 am
  
  Hi,
  
  Without seeing the data I would only be guessing at the issue. Try using the VLookup as a formula in a cell and compare the parameter values with the code parameters.
  
  This should get you closer to a resolution.
  
  Reply
Shawn Jass says:

May 11, 2018 at 4:13 pm

hello Paul,
What would be the best method if I wanted to send a message to be copied to a template Letter if 3 conditions come up? So, for example, I have 3 funds from a tab named FRED which has over 21,000 of data in it and when the commands come in to run in the template to create the letter, I need to search through this data in FRED to see if these 3 funds come up, and if they do come up, a separate letter needs to populate to the ‘Letter’ template. The spread sheet is using vLoopup throughout the program with only 2 macros running. I’ve been trying different techniques that I’ve been learning, but still a bit confused. Would you suggest running an Array through the data to search then inserting a if/then statement? Any help would be greatly appreciated.

Reply
salmon petrus says:

May 19, 2018 at 8:26 am

Hi Paul

How to fill different target cell with vlookup ?
I work in one sheet
This is example
range1
B3 = Name C3=P1 D3=P2 E3=P3
B4 = Jack C4=empty D4=empty E4=empty
B5 = Oneil C5=10 D5=empty E5=empty
B6 = Harry C6=30 D6=15 E6=empty
range2
B8=Jack C8=17
B9=Oneil C9=20
B10=Harry C10=18

I want a macro to fill the empty cell (C4 for Jack, D5 for Oneil and E6 for Harry) with value in C8,C9,C10
For next month, the macro will fill cell (D4 for Jack, E5 for Oneil and F6 for Harry) and so on.
In a single click of command button.

Thanks.

Reply
susan says:

June 18, 2018 at 7:09 pm

Hi, how can i make the column index dynamic? Rather than hardcode a column index number how do I have my macro use the column number from a specificed cell in the worksheet instead? smk

Reply
1. Paul Kelly says:
  
  June 20, 2018 at 1:40 pm
  
  You can use the Cells property of the worksheet.
  
  Reply
james says:

June 21, 2018 at 9:12 am

Hi, i am using the following VLOOKUP comment which works great! and really could do with this working in Hlookup however it doesnt work, are there different parameters for Hlookup?

Any help would be greatly appreciated 🙂

Reply
1. Paul Kelly says:
  
  June 21, 2018 at 11:42 am
  
  What you mean by “doesn’t work”?
  Create the Hlookup formula in excel first and when you are happy with it then add it to VBA.
  
  The parameters are the same apart from taking row instead of col.
  
  Reply
Irving Wilkinson says:

July 31, 2018 at 1:57 pm

Hello Paul, Perhaps you can help me. I have a number of workbooks I must “combine” into one master worksheet every day. Currently it takes me about an hour to accomplish. The issue is in order to combine them I must run Vlookups on each “ID” from the master worksheet for each workbook and each cell I want to update, and I am updating 26 cells per ID. Is there a way to quickly do this? Below is the macro I am using, again this only updates one cell where I need one macro to update all cells. I hope that makes sense.

Sub GetNotes()
Worksheets(“Sheet1”).Select

Range(“Q2”).Value =
“=IFERROR(VLOOKUP([@[Segment_id]],’C:\temp\EXCELTEMP\excelwork\NICOLE.xlsx]NICOLE’!$A:$U,17,0),IFERROR(VLOOKUP([@[Segment_id]],’C:\temp\EXCELTEMP\excelwork\JUSTIN.xlsx]JUSTIN’!$A:$U,17,0),IFERROR(VLOOKUP([@[Segment_id]],’C:\temp\EXCELTEMP\excelwork\WENDY.xlsx]WENDY’!$A:$U,17,0),IFERROR(VLOOKUP([@[Segment_id]],’C:\temp\EXCELTEMP\excelwork\UNDERCARRIAGE.xlsx]UNDERCARRIAGE’!$A:$U,17,0),IFERROR(VLOOKUP([@[Segment_id]],’C:\temp\EXCELTEMP\excelwork\PARTS.xlsx]PARTS’!$A:$U,17,0),IFERROR(VLOOKUP([@[Segment_id]],’C:\temp\EXCELTEMP\excelwork\MELISSA.xlsx]MELISSA’!$A:$U,17,0),IFERROR(VLOOKUP([@[Segment_id]],’C:\temp\EXCELTEMP\excelwork\CASEY.xlsx]CASEY’!$A:$U,17,0),IFERROR(VLOOKUP([@[Segment_id]],’C:\temp\EXCELTEMP\excelwork\SIStandard.xlsx]SIStandard’!$A:$U,17,0),IFERROR(VLOOKUP([@[Segment_id]],’C:\temp\EXCELTEMP\excelwork\[IRVING.xlsx]IRVING’!$A:$U,17,0),0)))))))))”
Range(“Q2”).Select
Selection.AutoFill Destination:=Range(“Q2:Q” & Cells(Rows.Count, “A”).End(xlUp).Row)

End Sub

Any help would be appreciated.

Reply
1. Paul Kelly says:
  
  August 1, 2018 at 4:40 am
  
  Hi Irving,
  
  There isn’t a quick way to do what you require.
  
  You need to create a small VBA application to accomplish this.
  
  Paul
  
  Reply
Brian says:

November 2, 2018 at 6:35 pm

Hi Paul,

Fun bug I just found: VLOOKUP does not work for exact match if “~” (Char(126)) is in the string. No idea why. I replaced ~ with chr(14) and now it works fine.

Reply
Marko says:

December 13, 2018 at 8:40 am

Hi Paul,

I would like to ask you, why doesn’t it work, if I use sRes as currency in your example where trying to trap the error with IsError(sRes), but it does work when trying to trap it with Err.Number = 0? In the first example it still gives me error, in the other, it works smoothly.

Thanks,

Reply
1. Paul Kelly says:
  
  December 20, 2018 at 12:32 pm
  
  Hi Marko,
  
  Application.VLookup returns a variant type. This means it decides the type at runtime. If there is an error it will return an error type. So if you use a currency type then it cannot be an error type.
  
  Paul
  
  Reply
Jake Menice says:

January 28, 2019 at 6:18 pm

I am trying to make a code that gives the user a simple template. The user will place a product name into cell b12 and the code will look on a file they selected for the product (note there are over several hundred products). How do I make it so that once they place the product name into the cell, the code will go to the other page and find the product?

Reply
1. Paul Kelly says:
  
  January 31, 2019 at 5:17 am
  Hi Jake,
  
  You use the worksheet change event to detect when the value in a cell has changed. Add this code to any worksheet in the VB editor:
```
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
```
  Paul
  Reply
James says:

May 2, 2019 at 4:58 pm

Great, great job sir! Each one of your articles has a lot of valuable information. Best I have found in the internet. BIG thanks!

Reply
erik says:

June 17, 2019 at 4:38 pm

Hi Paul,

instead of giving me the value of the found ”match,” i want it’s location, so where the match is found. how can i achieve this? many thanks! best regards, Erik

Reply
J E Koch says:

June 18, 2019 at 11:17 am

Hi Paul

Love your articles.

I often have a table where my first column (which contains the lookup value) contains both string and double values, so my code often returns Error 2042 if the variable type i was looking for was not a string.

It seems i have to first test what the value is then add either Cstr or Cdbl before my variable to get it working.

Do you know any other way around this?

Reply
1. J E Koch says:
  
  June 18, 2019 at 11:32 am
  
  What i would like to do is convert the values in the tables column to String and then search.
  
  I tried to format that column as text and even through the numbers (doubles) all go to the left, a vartype check shows them to still be doubles.
  
  Reply
2. Paul Kelly says:
  
  June 21, 2019 at 4:43 am
  
  If you mix data like this it will cause problems in the VLookup. You will to use some workaround to get past it.
  
  Reply
Nirajah Jegath says:

July 17, 2019 at 2:44 pm

Hello Paul,
I am using Lookup function in VBA. I have a scenario where I am trying to categorise the time into half hour slot. For example if 3:15pm then Lookup table should return 3pm _3:30pm. Could Lookup function do this as it isn’t an Exact match?

Reply
Shaun says:

November 20, 2019 at 7:09 am

Hi Paul.
I seem to misunderstand how to implement this.
I have workbook with four worksheets. The first has buttons linked to the macros, the second a “Temp” sheet, third my customer base with some details, forth, the output sheet.
What I do is copy to clipboard the data from an application, then click one of the buttons on the Main sheet. It pastes the data to the temp sheet and renames some of the columns.
I then click the Analyse button, which has a macro attached to copy specific columns to another sheet, where I need the VBA VLOOKUP to be applied to the column Customer Name, Column D. The output goes to Column E and F. This fills in the Holding Company and the Responsible Group.
I am searching through the web to find a solution, but am stumped at this time.
I’ve used On Error Resume Next in the renaming of the customer header as the data comes from two sources with different columns, and so far this works fine.
I need assistance including the VBA VLOOKUP to the code. If I can use the Dictionary method, it would be better. If you have time, I would appreciate it.
I am able to use inCell Vlookup, but I don’t want the VBA or Workbook broken as this will be used by various people. My Customer data is in Table “CustData”. I use Power Query to update this data when the file is opened.
I also need to include a userForm for lookup of individual customer info, the form is created already, but the coding has not yet been done.
What information do you need if able to assist?
Thank you for any help.

Reply
Ajay says:

November 21, 2019 at 6:05 am

Sir, I want Double VLookup formula in vba userform.[Example:- Combox1 that is as per books(accounting software) or as per portal and combobox2 that is for select month.]
Please give me best solution.
Thanks in advance..

Reply

Excel Macro Mastery

The Missing VBA Handbook

VBA VLookup – A Complete Guide

A Quick Guide to the VBA VLookup

Introduction

A Simple VBA VLookup example

The Parameters

Parameter 1: lookup_value

Using VLookup on a Date Type

Parameter 2: table_array

Parameter 3: col_index-num

Parameter 4: range_lookup

Dealing with Errors

Using WorksheetFunction

Using Application

VLookup Error Types

Errors and Causes

VBA VLookup Speed

What’s Next?

Get the Free eBook

54 comments

Leave a Reply Cancel reply