Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

Automate double vlookup using VBA

Hi Experts - 

I am reposting this question as this question did not get any responses from the experts. The moderator requested to delete the question and repost it.

The question is simple - I want to automate the vlookup function using VBA.
But the code should have option to specify the lookup value, range and relative column reference by the user through inputbox.

The code can be similar to the one given below taken from this link.
http://stackoverflow.com/questions/38639249/vlookup-in-vba-excel-vlookup

But, if data is very large normal vlookup will be very slow and so I want to use the double vlookup method as given in the below link. For this I need the following 2 adjustments to the below macro -

1 - Need to add Original order column and fill it with continuous numbers equal to number of items excluding header. This is required if we need to go back to the original order in which the data was given.

2 - Ask user if data is sorted and if the answer is no ask whether to sort it on the lookupvalue column. This is required as the double vlookup will only work on sorted columns.

3 - I also need vlookup error trapping like #N/A etc. in the code.

Sub VlookupMacro()
Dim subject As Range
Dim sheet As Range
Dim clm As Long
Set subject = Application.InputBox(prompt:="select Subject to be looked up", Type:=8)
Set sheet = Application.InputBox(prompt:="select tab/range", Type:=8)
Set myResults = Application.InputBox("Please select on the spreadsheet the first cell where you want your lookup results to start:", Type:=8)
clm = Application.InputBox(prompt:="Relative Column Reference", Type:=1)
'Change the range to the cell into which you want the formula placed.
'Range(myResults, myResults.Offset(FinalRow - FirstRow)).Formula = "=VLOOKUP(" & subject.Address(0, 0) & "," & sheet.Address(0, 0, xlA1, 1) & "," & clm & ",False)"
[b]Range(myResults, myResults.Offset(FinalRow - FirstRow)).Formula = "=IF" & "(VLOOKUP(" & subject.Address(0, 0) & "," & sheet.Address(0, 0, xlA1, 1) & "," & clm & ",True)" & "=" & Range(subject.Address(0, 0)).Value & ", " & "VLOOKUP(" & subject.Address(0, 0) & "," & sheet.Address(0, 0, xlA1, 1) & "," & clm & ",True)"[/b]
End Sub

Open in new window


I have attached an example sheet so that it will be easier to suggest the solutions.

Double Vlookup Method - https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/

Experts, please write a response for this and don't again make this as a neglected question.

Thanks in advance
VBA_Vlookup_Example.xlsm
0
Rocutana Rim
Asked:
Rocutana Rim
  • 4
  • 4
  • 3
2 Solutions
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
What Excel version you are using?
If you are dealing with a large data set, I would suggest you go for Power Query as an alternative method for VlookUp.
0
 
Rocutana RimAuthor Commented:
@Subodh Tiwari (Neeraj) - Thanks for your response.

I use MS Excel 2007 and use the Vlookup function often as my work is related to data. I will not always work with large data, but as I said earlier I use Vlookup function often. So If I get a generic VBA code to automate this, my work will become more easy.

Kindly suggest some solution for this.

Thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Why do you need VBA to input the Vlookup Formula on the Lookup Sheet while you can simply place the formula directly?

Anyways while I really don't understand the your purpose behind this, please find the attached with a button on Lookup Sheet called "VlookUp".
When this button is clicked, you will be prompted to input a Lookup Value, Lookup Table, Formula Cell, Column Index and Column on Input Sheet to have the original sort order.

The sorting is needed on Input Sheet not on Lookup Sheet. The code will place the sequence numbers on the Input Sheet starting from Row2 so that you can sort that particular column in Ascending order to get the original sort order back.
VBA_Vlookup_Example.xlsm
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
ProfessorJimJamCommented:
please see attached.

Option Explicit
Sub MACRObyJIMJAM()

  ' By ProfessorJimJam  Answer to Question 28972914  October 3rd 2016
Dim LookupValue As Range
Dim LookupArray As Range
Dim COLINDEX As Long
Dim firstrow As Integer
Dim finalrow As Integer
Dim ResultRange As Range
Dim NotFound As String
NotFound = """Not Found"""
On Error Resume Next
Set LookupValue = Application.InputBox(prompt:="select LookupValue to be looked up", Type:=8)
On Error GoTo 0
If LookupValue Is Nothing Then
MsgBox " you cancelled it"
Exit Sub
End If
On Error Resume Next
Set LookupArray = Application.InputBox(prompt:="select tab/range", Type:=8)
On Error GoTo 0
If LookupArray Is Nothing Then
MsgBox " you cancelled it"
Exit Sub
End If
Dim OutPut As Integer
OutPut = MsgBox("Is the first column of LookupRange sorted?", vbYesNo, "Question of Sort")
If OutPut = 7 Then  '7 is No  &  6 is Yes
MsgBox "LookupRange Sort is Complete!"
With LookupArray
.Sort Key1:=.Columns(1), Header:=xlNo
End With
Else
End If
On Error Resume Next
Set ResultRange = Application.InputBox("Please select on the range first cell where you want your lookup results to start:", Type:=8)
On Error GoTo 0
If ResultRange Is Nothing Then
MsgBox " you cancelled it"
Exit Sub
End If
On Error Resume Next
COLINDEX = Application.InputBox(prompt:="Relative Column Reference", Type:=1)
On Error GoTo 0
If COLINDEX < 1 Then
MsgBox " you cancelled column index number required number"
Exit Sub
End If
On Error GoTo SingleCell:
 finalrow = Range(ResultRange.Offset(0, -1), ResultRange.Offset(0, -1).End(xlDown)).Rows.Count
On Error GoTo 0
Range(ResultRange, ResultRange.Resize(finalrow)).Formula = "=IF(" & "VLOOKUP(" & LookupValue.Address(0, 0) & "," & LookupArray.Address(True, True, xlA1, False) & "," & 1 & ",True)" & "=" & LookupValue.Address(0, 0) & "," & "VLOOKUP(" & LookupValue.Address(0, 0) & "," & LookupArray.Address(True, True, xlA1, False) & "," & COLINDEX & ",True)" & "," & NotFound & ")"
SingleCell:
ResultRange.Formula = "=IF(" & "VLOOKUP(" & LookupValue.Address(0, 0) & "," & LookupArray.Address(True, True, xlA1, False) & "," & 1 & ",True)" & "=" & LookupValue.Address(0, 0) & "," & "VLOOKUP(" & LookupValue.Address(0, 0) & "," & LookupArray.Address(True, True, xlA1, False) & "," & COLINDEX & ",True)" & "," & NotFound & ")"
End Sub

Open in new window

EE.xlsm
0
 
Rocutana RimAuthor Commented:
Thanks Neeraj & ProfessorJimJam for your replies. Both of the solutions are great and I will select both as solution.

I got one small doubt regarding the above process. I re-read the Charles page, but couldn't get the correct answer for that.

My doubt is that for the above process, should both the columns be sorted or just the column with the lookup value. Kindly clarify.

Thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome.
Only sheet with the lookup table should be sorted.
0
 
ProfessorJimJamCommented:
you are welcome.
0
 
Rocutana RimAuthor Commented:
Thank you both of you for clearing my doubt.

@Neeraj - I request one more update to your code. When I used your code with around 160K, after the sort order input box, my excel hangs or it becomes very slow.

@ProfessorJimJam - Sir, your code also works fine but can you update a Original order as requested in previous comment.

Can't we just put consecutive numbers for both the input and lookup columns in the rightmost blank column and then sort?

Also, Can the code be extended for multiple workbooks i.e selecting input workbook and mapping workbbok. The inputbox works only for the active workbook.

I know that I am asking for more effort and time of yours, but please don't feel bad. I am a newbie in VBA and so exploring various methods for doing things.

I had another question related to this - automate vlookup using dictionary but following the same above mentioned process.

I had asked both questions previously, but got no responses, so I reposted one now and got your responses and if possible kindly look into this and provide a method for doing this.

Thanks Once Again
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The code will take time for that much of data.
Try to add DoEvents line before each line of code which place the formula on the sheet.
0
 
ProfessorJimJamCommented:
@Vijayendra S. Murthy

it is simple.  use a helper column next to the data and put 1 and then fill series 1,2,3,4,5,6,6,7,8,9,10 and so on.

then whenever you have run your macro and you want to return to original sort, then simply sort the 1,2,3,4 column back to ascending order and it will move back original state.
0
 
Rocutana RimAuthor Commented:
Hi Experts - Thanks for your response.

Can you please look into my other questions about extending this to multiple workbooks and using Dictionary to do vlookup.

Thanks once again for all your help.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now