| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup returns unexpected result
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,1 330,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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup returns unexpected result
Dear David
This should work. I have sorted the lookup elements =LOOKUP(MatRoster!C8,{7,8,9,13,17,730,830,1330,143 0,2115,2130,2230,2245;"AM","AM","AM","PM","PM","AM ","AM","PM","PM","ND","ND","ND","ND"}) If this post helps click Yes --------------- Jacob Skaria "David Ryan" wrote: 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,1 330,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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup returns unexpected result
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" wrote: Dear David This should work. I have sorted the lookup elements =LOOKUP(MatRoster!C8,{7,8,9,13,17,730,830,1330,143 0,2115,2130,2230,2245;"AM","AM","AM","PM","PM","AM ","AM","PM","PM","ND","ND","ND","ND"}) If this post helps click Yes --------------- Jacob Skaria "David Ryan" wrote: 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,1 330,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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup returns unexpected result
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 --------------- Jacob Skaria "David Ryan" wrote: 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" wrote: Dear David This should work. I have sorted the lookup elements =LOOKUP(MatRoster!C8,{7,8,9,13,17,730,830,1330,143 0,2115,2130,2230,2245;"AM","AM","AM","PM","PM","AM ","AM","PM","PM","ND","ND","ND","ND"}) If this post helps click Yes --------------- Jacob Skaria "David Ryan" wrote: 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,1 330,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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup returns unexpected result
Hi Jacob
Appreciate your time and yes helps. Again thanks "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "David Ryan" wrote: 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" wrote: Dear David This should work. I have sorted the lookup elements =LOOKUP(MatRoster!C8,{7,8,9,13,17,730,830,1330,143 0,2115,2130,2230,2245;"AM","AM","AM","PM","PM","AM ","AM","PM","PM","ND","ND","ND","ND"}) If this post helps click Yes --------------- Jacob Skaria "David Ryan" wrote: 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,1 330,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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Unexpected Result | Excel Worksheet Functions | |||
| sumproduct with boolean criteria returns unexpected 0 | Excel Discussion (Misc queries) | |||
| FV Function result is unexpected. | Excel Worksheet Functions | |||
| vlookup based on random result returns incorrect result | Excel Worksheet Functions | |||
| Unexpected result | Excel Worksheet Functions | |||
Linear Mode
