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

On Fri, 18 Aug 2006 04:06:02 -0700, Andy
wrote:

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


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
__________________________

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

Andy wrote:
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