LOOKUP formula

[Andy]

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

 

[Aladin Akyurek]

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")