Lookup returns unexpected result

[David Ryan]

Hi Guys its me again sorry. Below is the function and the results obtained. I
have changed the cell format to all sorts with no effect.

Hope someone can help. Happy to email the spreadsheet if that will help, no
laughing at my probable poor formulas.

=IFERROR(LOOKUP('MatRoster'!C7,{7,730,8,830,9,13,1330,1430,17,2115,2130,2230,2245},{"AM","AM","AM","AM","AM","PM","PM","PM","PM","ND","ND","ND","ND"}),0)

7 Returns AM
730 Returns PM should be AM
8 Returns AM
830 Returns PM should be PM
9 Returns AM
13 Returns PM
1330 Returns PM
1430 Returns PM
17 Returns PM
2115 Returns PM
2130 Returns ND
2230 Returns ND
2245 Returns ND

 

[Jacob Skaria]

Dear David

This should work. I have sorted the lookup elements

=LOOKUP(MatRoster!C8,{7,8,9,13,17,730,830,1330,1430,2115,2130,2230,2245;"AM","AM","AM","PM","PM","AM","AM","PM","PM","ND","ND","ND","ND"})


If this post helps click Yes

 

[David Ryan]

Hi Jacob that seems to work well.
If its not to detailed or much trouble would love an explanation why so i do
not make a similar mistake.

THANKS HEAPS!!

 

[Jacob Skaria]

Dear David

The lookup range within this function is a single row or single column of
data that is sorted in ascending order and if not the function will return
the incorrect value. If the Lookup function cannot find an exact match it
chooses the largest value in the range that is less than or equal to the
value. If the value is smaller than all of the values in the lookup range
then the Lookup function will return #N/A.

Hope this helps..

If this post helps click Yes

 

[David Ryan]

Hi Jacob
Appreciate your time and yes helps. Again thanks