Nested LOOKUP function returns incorrect data

**TheAkwardOne** · 04-22-2008, 01:53 PM

I setup this function to pull my staff's schedule an auto generate the forms i need however the lookup function works properly sometimes and improperly others i checked the syntax and it is correct how ever the wrong data is continually returned. I have attached the file if anyone can help. the VISUAL PLAN tab is the one contaning the formula and it pulls data from PLAN, CURENT SCHEDULE, AND CATAGORY SUMMARY however the only formulas hat i have a problem with are the ones referencing CURENT SCHEDULE please help

**NBVC** · 04-22-2008, 02:00 PM

For Lookup() to work properly the first column of the lookup table must be in ascending order.... and if you want an exact match, then Lookup() might not always give it to you...try Vlookup() instead...

e.g.

Please Login or Register  to view this content.

copied down.

I also noticed that you did not make your lookup table absolute... you need to add the $ signs in front of the row and column references before copying down in order for the table to be "frozen".

**TheAkwardOne** · 04-22-2008, 11:57 PM

Hey thanks a lot for the help i really appreciate it but the formula that i am really having issues with are the ones in the shift column of VISUAL PLAN it looks like everything is fine but when you change M30 in PLAN is when everything gets wierd. for example when M30[PLAN] is 5(Thursday),6(Friday), or 7(Saturday) everything goes haywire it either shows the wrong info or it says #n/a and when i trace the error and try to debug everything apears normal if you could take another look that would be wonderful i've spent 2 hours just on these formulas today and i just can't grasp it. Thanks a lot

**NBVC** · 04-23-2008, 08:01 AM

Well, not sure if you just copied sheets from another workbook to this sample book, but the tab name "curent schedule" was differently spelled in the formula... the formula had it as "curent shedule" (missing the "c" in schedule)....not to mention that "curent" is mispelled also.

The second thing I noticed is that the lookups in the current formula you show are referencing columns M and N in the Plan sheet, but I think they should be referencing L and M....

Anyways this is the formula revised in D31 of VACATION PLAN and, I think, pulling the right info:

Please Login or Register  to view this content.

**TheAkwardOne** · 04-23-2008, 10:14 PM

i see the misspelling that you had mentioned and again not the problem Also i thought it would be a good idea to explain exactly what i am trying to do and what it actual does so...here it is:
D31:D34 of VISUAL PLAN looks up what day it is by looking at the number in M30 of PLAN then looks up that number in M36:M42 and finds the day coresponding to that number in N36:N42 Once the function finds out what day it is, it looks for that day in Curent Shedule (spelled wrong i know lol) B4:H4 and then finds that persons info in that column. So by changing PLAN M30 i am telling the function what day it is. The issue is that on thursday (M30=5) it pulls incorrect, on friday(M30=6) it pulls #N/A and on saturday(M30=7) it pulls the same incorrect info from thursday, all other days of the week pull correct info!

grrrrr So i'm not sure whats happening i wish excel had a better debugger so i could figure it out

**TheAkwardOne** · 04-23-2008, 10:35 PM

Here is the revised version with the proper changes. To help things along i made all cells being referenced in these formulas red and also in current schedule i changed the schedule to all be the same so that should also make things easier Thanks a bunch
-Matt

**NBVC** · 04-24-2008, 08:04 AM

The problem is that when you use Lookup() function, the Lookup table values must be in ascending order for it to work properly... so the inner Lookup() function works because the days of the week are listed in ascending order in Plan!M36:M42, but the outer Lookup() function doesn't work because the text version of the days of the week are not listed alphabetically, in ascending order... so we must then revert to Hlookup() which has the option to specify the lookup table is not in any particular order and therefore lookup just the exact match.....

so in Visual Plan!D31 enter formula:

Please Login or Register  to view this content.

Notice with this function you specify the entire table array starting from the row with the lookup values. The 3rd argument is the vertical position in number of rows from the row with the lookup values (i.e from Row 4). The 0 at the end tells the function to look for an exact match and that the table need not be in any specific order.

So you will have to adjust only the Vertical position number when you copy the formula to other cells.

Have a look at help files in Excel for this function and the related Vlookup function which works horizontally.

Hope this helps.

**TheAkwardOne** · 04-26-2008, 08:01 PM

HAHA I finaly figured it out and it was so silly it was staring me in the face the whole time. VLOOKUP stands for what??? Vertical Lookup I was looking for info horizantally i reversed the lookup and the problem corrected itself.

YAY Thanks a lot for all your help i really appreciat it. -Matt

**NBVC** · 04-27-2008, 08:58 PM

Originally Posted by TheAkwardOne

HAHA I finaly figured it out and it was so silly it was staring me in the face the whole time. VLOOKUP stands for what??? Vertical Lookup I was looking for info horizantally i reversed the lookup and the problem corrected itself.

YAY Thanks a lot for all your help i really appreciat it. -Matt

Isn't that what I said I day before ?

**TheAkwardOne** · 04-29-2008, 12:15 AM

when i used the formula you had adjusted it fixed almost all the problems but i was still getting an #n/a on sunday just not any other day so in the formula itself i changed how it searches instead of looking for what day it first i look at who's on the schedule first, by doing the all the #n/a's went away and the formula worked as i had imagined. Thanks for all the help!