# Lookup

#### Peter1973

##### Well-known Member
I am trying to get a lookup to work differently, I have :

=VLOOKUP(B5,'Order Creation'!\$C\$6:\$P\$4004,2,FALSE)

Is it possible for the lookup to return the value not in the corresponding cell in column 2 but in the cell that is 5 across and 5 down so if B5 is found in cell C6 it would return the value in cell G10, Hope this is clear.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Peter_SSs

##### MrExcel MVP, Moderator
Untested, but is this it?

=INDEX('Order Creation'!G:G,MATCH(B5,'Order Creation'!C:C,0)+4)

#### Peter1973

##### Well-known Member
Can you possibly explain how this works as as when I try to change this to look at a different row it gives me very strange results.

Thanks

##### MrExcel MVP
Try...
Code:
``````=IF(ISNUMBER(MATCH(B5,'Order Creation'!\$C\$6:\$C\$4004,0)),
INDEX('Order Creation'!\$H\$6:\$H\$4004,
MATCH(B5,'Order Creation'!\$C\$6:\$C\$4004,0)+5)),

#### Peter_SSs

##### MrExcel MVP, Moderator
... when I try to change this to look at a different row it gives me very strange results.
Row? Do you mean column?

Can you possibly explain how this works ...
Sure, I'll try ...

1. MATCH(B5,'Order Creation'!C:C,0)
This looks down column C on 'Order Creation' sheet for the B5 value and returns how far down the column it is found. That is, it returns the row number where the B5 number is found.

2. MATCH(B5,'Order Creation'!C:C,0)+4
This adds 4 to the previous result. So if the B5 value is found in cell C6 (that is row 6), this part of the formula will return 10

3. =INDEX('Order Creation'!G:G,MATCH(B5,'Order Creation'!C:C,0)+4)
This returns the value from column G of the 'Order Creation' sheet in the row obtained in the last step. For the example given, this was the number 10 so the formula returns the value from G10 - as your example requested.

Last edited:

#### Peter1973

##### Well-known Member
I have put this in but it is telling me there is an error in the "Not Found")

##### MrExcel MVP
I have put this in but it is telling me there is an error in the "Not Found")

A paren too many...
Code:
``````=IF(ISNUMBER(MATCH(B5,'Order Creation'!\$C\$6:\$C\$4004,0)),
INDEX('Order Creation'!\$H\$6:\$H\$4004,
MATCH(B5,'Order Creation'!\$C\$6:\$C\$4004,0)+5),

#### Peter1973

##### Well-known Member
Thanks that is great

Replies
2
Views
357
Replies
5
Views
171
Replies
0
Views
447
Replies
2
Views
353
Replies
0
Views
360

1,190,911
Messages
5,983,526
Members
439,848
Latest member
timmyo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back