Offset instead off 2nd vlookup

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
I am struggling with this one.

In the following:

in column X I have zip codes for each customer order. For each zip I do 2 vlookup against sheet Lat and Long. The first to find Latitude and 2nd to find longitude.

How can I change the code to get the offset value of the vlookup and not do a 2nd vlookup?

Example -

X3 = 54321 vlookup X3 against sheet "Long & Lat" Column A , return results of column B & C to Y3 and Z3

Column A is list of zip codes
Column B is latitude for zip codes
Column C is longitude for zip codes

Set Rng = Sheets("Lat & Long").Columns("A:C")
With Sheets("Customer Orders")
For Each cell In Range("X3:X" & Range("A1").Value)
On Error Resume Next
If cell.Value > " " Then
.Range("Y" & cell.Row).Value = _
WorksheetFunction.VLookup(cell, Rng, 2, False)
.Range("Z" & cell.Offset(0, 1).Row).Value = _
WorksheetFunction.VLookup(cell, Rng, 3, False)
End If
Next cell
End With


Thanks,

Kurt
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
I can't really help on the VBA side, but you could determine the row number once with

=MATCH(x3,A1:A10,0)

Store this result and use it in the index function to bring back
=INDEX(X:Y,[match result],1) for X and
=INDEX(X:Y,[match result],2) for Y
 

Watch MrExcel Video

Forum statistics

Threads
1,118,680
Messages
5,573,628
Members
412,539
Latest member
itchy00
Top