# Offset instead off 2nd vlookup

#### stapuff

##### Well-known Member
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

### 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
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

Replies
3
Views
142
Replies
7
Views
1K
Replies
4
Views
123
Replies
5
Views
148
Replies
6
Views
199