lookup formula unexpected results

[David Ryan]

Hi Folks hope someone can help. One of you guys usually can. So thanks in
advance
The following formula provides unexpected results.
=IF(ISERROR(LOOKUP('Hotel
Services'!AD91,{"COOK","D1","D2","D3","D4","D5","K1","K2"},{10,8,8,8,5.15,3.5,8,5})),0,LOOKUP('Hotel
Services'!AD91,{"COOK","D1","D2","D3","D4","D5","K1","K2"},{10,8,8,8,5.15,3.5,8,5}))
spreadsheet developed in 2007 but others who access use 2003 office
When COOK, D1 etc are entered it works great
The following are also entered into the spreadsheet
AL returns 0 correct
ADO returns 0 correct
LSL returns 5 should be 0
S returns 5 should be 0
PH returns 5 should be 0
C returns 0 correct
C4 returns 0 correct
M returns 5 should be 0
F returns 3.5 should be 0

for some reason the error checking in the formula seems to be ?? incorrect.
hope someone can help

 

[Harlan Grove]

=IF(ISERROR(LOOKUP('Hotel Services'!AD91,
{"COOK","D1","D2","D3","D4","D5","K1","K2"},
{10,8,8,8,5.15,3.5,8,5})),0,LOOKUP('Hotel Services'!AD91,
{"COOK","D1","D2","D3","D4","D5","K1","K2"},
{10,8,8,8,5.15,3.5,8,5})) ....
The following are also entered into the spreadsheet
AL returns 0 correct
ADO returns 0 correct

These throw errors because AL and ADO are lexically less than COOK.

LSL returns 5 should be 0

....

That may be what you want, but LOOKUP matches it to K2 because K2 <=
LSL.

LOOKUP uses range matching, that is, it fails to match any string
lexically less than COOK, but matches any string lexically equal to
COOK to just less than D1 to COOK. For example, CUP, CXYZ and D would
all match COOK because they fall between COOK and D1.

You seem to want exact matching. If so, use

=IF(OR('Hotel Services'!AD91=
{"COOK","D1","D2","D3","D4","D5","K1","K2"}),
LOOKUP('Hotel Services'!AD91,
{"COOK","D1","D2","D3","D4","D5","K1","K2"},
{10,8,8,8,5.15,3.5,8,5}),0)

 

[Harlan Grove]

=IF(ISERROR(LOOKUP('Hotel Services'!AD91,
{"COOK","D1","D2","D3","D4","D5","K1","K2"},
{10,8,8,8,5.15,3.5,8,5})),0,LOOKUP('Hotel Services'!AD91,
{"COOK","D1","D2","D3","D4","D5","K1","K2"},
{10,8,8,8,5.15,3.5,8,5})) ....
The following are also entered into the spreadsheet
AL returns 0 correct
ADO returns 0 correct

These throw errors because AL and ADO are lexically less than COOK.

LSL returns 5 should be 0

....

That may be what you want, but LOOKUP matches it to K2 because K2 <=
LSL.

LOOKUP uses range matching, that is, it fails to match any string
lexically less than COOK, but matches any string lexically equal to
COOK to just less than D1 to COOK. For example, CUP, CXYZ and D would
all match COOK because they fall between COOK and D1.

You seem to want exact matching. If so, use

=IF(OR('Hotel Services'!AD91=
{"COOK","D1","D2","D3","D4","D5","K1","K2"}),
LOOKUP('Hotel Services'!AD91,
{"COOK","D1","D2","D3","D4","D5","K1","K2"},
{10,8,8,8,5.15,3.5,8,5}),0)

 

[Jacob Skaria]

The number of items within the lookup vector and result vector should be same..

 

[Jacob Skaria]

The number of items within the lookup vector and result vector should be same..

 

[David Ryan]

Hi jacob they are unless I am missing something

The number of items within the lookup vector and result vector should be same..

 

[David Ryan]

Hi jacob they are unless I am missing something

The number of items within the lookup vector and result vector should be same..

 

[Jacob Skaria]

=IF(ISERROR(MATCH('Hotel

Services'!AD91,{"COOK","D1","D2","D3","D4","D5","K1","K2"},0)),"",LOOKUP('Hotel

Services'!AD91,{"COOK","D1","D2","D3","D4","D5","K1","K2"},{10,8,8,8,5.15,3.5,8,5}))

Would suggest you to use VLOOKUP with the details entered to a range.


If this post helps click Yes

 

[Jacob Skaria]

=IF(ISERROR(MATCH('Hotel

Services'!AD91,{"COOK","D1","D2","D3","D4","D5","K1","K2"},0)),"",LOOKUP('Hotel

Services'!AD91,{"COOK","D1","D2","D3","D4","D5","K1","K2"},{10,8,8,8,5.15,3.5,8,5}))

Would suggest you to use VLOOKUP with the details entered to a range.


If this post helps click Yes