LOOKUP formula

A

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
 
A

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

lookup number greater than 2
TYPE function 3
Lookup Function help 1
match formula 2
Lookup Function 1
vlookup and match first value greater than 4
Lookup Function Help 1
Vlookups to return a N/A value 3

Top