Vlookup code....

[ExcelNovice]

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.

 

[Barrie Davidson]

Why not just use a VLOOKUP?

 

[ExcelNovice]

Hi Barrie,

How could I do the same thing using Vlookup....

Thanks.

 

[Barrie Davidson]

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]

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]

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.

 

[ExcelNovice]

Great. Thank you.