Lookup

[Peter1973]

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.

 

[Peter_SSs]

Untested, but is this it?

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

 

[Peter1973]

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

 

[Aladin Akyurek]

Try...

=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)),
    "Not Found")

 

[Peter_SSs]

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

 

[Peter1973]

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

 

[Aladin Akyurek]

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

A paren too many...

=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),
    "Not Found")

 

[Peter1973]

Thanks that is great