LOOKUP formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy - 18 Aug 2006 12:06 GMT

Am using LOOKUP formula to return values and it all seems to work fine.

=LOOKUP(B19,'[andrew v2.xls]Sheet1'!$B$5:$B$28,'[andrew
v2.xls]Sheet1'!$C$5:$C$28)

However, when dragging and copying the formula, if the Lookup_value is not
in the Lookup_vector, the formula just returns the last lookup that it could
return. i.e. if the return value is 100 for a lookup of A21, if A22 does not
exist in the lookup_vector, instead of returning N/A or something it will
return 100...

Any help appreciated.

Thanks

Reply to this Message

Richard Buttrey - 18 Aug 2006 12:20 GMT

>Am using LOOKUP formula to return values and it all seems to work fine.
>
[quoted text clipped - 10 lines]
>
>Thanks

That's the way LOOKUP works. If the lookup value doesn't exist. It
looks for the next highest 'value' and drops back one row.

Much better IMO to use VLOOKUP and include a ",False" as the last
term.

i.e.

=VLOOKUP(B19,'[andrew v2.xls]Sheet1'!$B$5:$B$28,'[andrew
v2.xls]Sheet1'!$C$5:$C$28,False)

If you don't want to see #N/A if the value doesn't exist, you can wrap
the formula in an IF(ISERROR) statement.

i.e.

If(ISERROR(VLOOKUP(B19,'[andrew v2.xls]Sheet1'!$B$5:$B$28,'[andrew
v2.xls]Sheet1'!$C$5:$C$28,False)),"Value Not
Present",VLOOKUP(B19,'[andrew v2.xls]Sheet1'!$B$5:$B$28,'[andrew
v2.xls]Sheet1'!$C$5:$C$28,False))

HTH

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Reply to this Message

Aladin Akyurek - 19 Aug 2006 00:39 GMT

If B2:C28 is sorted in ascending order on Sheet1 in andrew v2.xls...

=IF(LOOKUP(B19,'[andrew v2.xls]Sheet1'!$B$5:$B$28)=B19,
LOOKUP(B19,'[andrew v2.xls]Sheet1'!$B$5:$B$28,
'[andrew v2.xls]Sheet1'!$C$5:$C$28),
"Not Found")

Otherwise:

=IF(ISNUMBER(MATCH(B19,'[andrew v2.xls]Sheet1'!$B$5:$B$28,0)),
INDEX('[andrew v2.xls]Sheet1'!$C$5:$C$28,
MATCH(B19,'[andrew v2.xls]Sheet1'!$B$5:$B$28,0)),
"Not Found")

> Am using LOOKUP formula to return values and it all seems to work fine.
>
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message