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.

=IF('[MPP Grocery Packet CAO 2.xls]PLAN'!D24<1,"",VLOOKUP('[MPP Grocery Packet CAO 2.xls]PLAN'!D24,'[MPP Grocery Packet CAO 2.xls]PLAN'!$B$46:$C$57,2,False))

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:

=LOOKUP(LOOKUP(PLAN!$L$30,PLAN!$L$36:$L$42,PLAN!$M$36:$M$42),'Curent Schedule'!$B$4:$H$4,'Curent Schedule'!B15:H15)

**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