You are here:

Excel/Excel2007 VBA - Vlookup?

Advertisement


Question
QUESTION: Hi Tom,
I’m using Excel 2007 – VBA. I think this calls for VLookup, but it seems that Col_Index_Num will only return one column and I need 4 columns. I could maybe  run it 4 times, but with 50,000+ rows,  it would be quite a grind.
Column A of Wb2Sh1 contains “ItemNumber” and columns E:H contain “prices”. Column A of Wb1Sh1 also contains “ItemNumber”. When the code finds a matching “ItemNumber”,  I want it to copy from columns E:H (Wb2Sh1) to columns B:E (of Wb1Sh1). I’ve got both “ItemNumber” columns in GeneralNumber format. Both worksheets have a header row. Both worksheets also have other columns that have data, but I only mentioned the relevant ones here. I hope you can help.
Thanks!
Doug

ANSWER: doug

Assume wb2sh1 and wb1sh1 are sheet names in the same workbook

B2 of wb1sh1: =iferror(vlookup($A2,wb2sh1!$A$2:$H$51000,columns()+4,False),"")

drag fill the formula to cell E2

now select B2:E2 and drag fill down to the last row with data in column A.

I can't say whether it will be quite a grind or not.

If this is a one time operation, then select B:E and copy, then do a paste special and select values to replace the formulas with the values returned.

--
Regards,
Tom Ogilvy






---------- FOLLOW-UP ----------

QUESTION: Thanks Tom
I was looking for VBA script. This will be part of an existing macro.
However, I did try pasting your formula in b2 (just to see), and Excel didn't like it. I'm pretty sure it objected to "columns()". I tried changing it to columns(e) (and also other column letters), and the program accepted it but I got no results.
Thanks! Doug

Answer
Doug,

that was a typo (sorry)

columns() should have been column().  Also the +4 should be +3

B2 of wb1sh1: =IFERROR(VLOOKUP($A2,wb2sh1!$A$2:$H$51000,COLUMN()+3,FALSE),"")

If I was going to do it in VBA, I probably would paste in the formula in all the cells and then replace the results.

A simple example would be

Sub ABC()
 with worksheets("wb1sh1").Range("B2").Resize(51000,4)
   .formula = "=iferror(vlookup($A2,wb2sh1!$A$2:$H$51000,column()+3,False),"""")"
   .Formula = .value
 end with
End Sub

As a test, I select B2 on wb1sh1 went to the name box and typed in
B2:H51000 <enter>
this selected all the cells in the specified range
whent to the formula bar and enter =IFERROR(VLOOKUP($A2,wb2sh1!$A$2:$H$51000,COLUMN()+3,FALSE),"")
and finished with Ctrl+Enter.  It took about a minute and a half to fill and calculate the formulas.   The results were still formulas

I then ran the above macro and it took a little less than a minute to apply the formulas and replace the calculated values with the returned values. So the results were numeric constants.

I didn't have any other formulas in the workbook.  If I did, those could cause things to run slower.

========<added>==========
You might have been thinking of a macro like this one that copies all 4 columns at once  

Sub abcd()
Dim sh As Worksheet, cell As Range, r2 As Range
Dim rng As Range
Set sh = Worksheets("wb2sh1")
Set r2 = sh.Range("A1", sh.Cells(sh.Rows.Count, 1).End(xlUp))
For Each cell In Range("A2:A51000")
 Set rng = r2.Find(What:=cell, _
         After:=r2(1), _
         LookIn:=xlValues, _
         LookAt:=xlWhole, _
         SearchOrder:=xlByRows, _
         SearchDirection:=xlNext, _
         MatchCase:=False)
 If Not rng Is Nothing Then
   rng.Offset(0, 4).Resize(1, 4).Copy cell.Offset(0, 1)
 End If
Next
End Sub

I had to cut that off after about 3 minutes and it was only at about row 3500.  The answers were correct, but it was really really slow compared to the macro above or even the manual approach that I described.

It loops through the cells in column A of wb1sh1 (51000 entries). for each cell, it  Finds the match in column A of wb2sh1 (51000 entries) and then copies the 4 columns from that row back to wb1sh1 and pastes them next to the cell being processed.  

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.