Offset instead off 2nd vlookup

[stapuff]

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

 

[IML]

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