LOOKUP formula

[Guest]

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

 

[Richard Buttrey]

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
__________________________

 

[Richard Buttrey]

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
__________________________

 

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

 

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