Vlookup code....

ExcelNovice

Well-known Member
Joined
May 12, 2002
Messages
578
Hi all,

I'm trying to tweak the following code so that when I enter data in cells A2:A1000, Vlookup will search the data on sheet 2 and if the value is found, shows the corresponding data on sheet 1 in cells B2:11000. The macro works fine now, but it only allows me to enter data in cell A2 only and then it searches the data on sheet 2, and if the value is found it shows it on sheet 1 in cells B2:i2.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [A2]) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim LastUsed As Long
LastUsed = Cells(Rows.Count, 2).End(xlUp).Row
Range("B1:I" & LastUsed).Clear
With Sheet2
.Columns("A:I").AutoFilter Field:=1, Criteria1:=Target.Value
.[B:I].Copy [B1]
.Cells.AutoFilter
End With
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Thanks for your help.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
In B2 (and copy down)

=VLOOKUP(A2,'Sheet2'!$A$1:$B$1000,2,0)

Assumptions:
• Your lookup table is in the range 'Sheet2'!$A$1:$B$1000
• The value being looked up (A2) will be in column A of the lookup table
• The value being returned from the lookup table is in the second column (column B).

Just make the necessary changes to the formula to accomodate your data.

Regards,
 

ExcelNovice

Well-known Member
Joined
May 12, 2002
Messages
578

ADVERTISEMENT

Thanks Barrie.
How do I change this so the value being returned is from the second, third and fourth columns on the lookup table (B, C, D & E)?
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
ExcelNovice said:
Thanks Barrie.
How do I change this so the value being returned is from the second, third and fourth columns on the lookup table (B, C, D & E)?

Change the second last variable (and your data range of course). So to return a value from column C
=VLOOKUP(A2,'Sheet2'!$A$1:$C$1000,3,0)
column D
=VLOOKUP(A2,'Sheet2'!$A$1:$D$1000,4,0)
etc.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,099
Members
412,441
Latest member
kelethymos
Top