Forum Discussion
Formula to retrieve data from several sheets and return values from given criteria.
I am trying to get data from each of our areas of the school staff timetables EY MY LY and summaries what days each staff member works where to another sheet, as staff move around. I would appreciate some help please.
This is how far I got:
=IFERROR(VLOOKUP(A10,EY!$C$3:$G$33, 2, FALSE), IFERROR(VLOOKUP(A10,MY!$C$3:$G$33, 2, FALSE), "Not Found"))
This just returns;
I tried to add in the same formula for LY but it is saying too many arguments, this is why Michelle is Not Found.
Will I also be able to return the area they are in on each of the days ?
Any assistance would be very much appreciated.
3 Replies
I agree that your formula structure works if the formula is entered correctly. But do you actually need/have the staff member's name entered twice on the source worksheets (both columns C and D)?
See the attached workbook for more information.
Edit: The forum software is "losing" attachments frequently. Trying again...
Edit: The attachment was "lost" again. Including a link to OneDrive instead: 2025-12-11 SWEX staff location by day.xlsx
Ohhhhh Wow !! thank you so much for all the info. I really do appreciate it. Lots of learning for me in all that which is fabulous.
Yes, you are correct. I dont really need the names to come up every day at all. Colomn A can just say the name of which needs to be searched in the 3 other sheets and it only needs to be days of the week to return what area each staffer is in. But I couldnt fathom the formula for that.
I will go through all your info and try it all. Im so pleased about this into! I am a self confessed spreadsheet nerd, though in the scheme of things I really do have very little knowledege with formulas and I sit at a very basic level.
You should be able to nest a 3rd IfError statement for the sheet LY. Perhaps you were missing a comma or misplaced bracket close?
Replace the “Not Found” with:
IFERROR(VLOOKUP(A10,LY!$C$3:$G$33,2, FALSE),"Not Found")
The complete formula should look like:=IFERROR(VLOOKUP($A10,EY!$C$3:$G$33,2, FALSE),IFERROR(VLOOKUP($A10,MY!$C$3:$G$33,2,FALSE),IFERROR(VLOOKUP($A10,LY!$C$3:$G$33,2,FALSE),"Not Found")))
To return the location instead, replace the IfError(…) (Do this if there is an error) with If(IsError(…)) Check if there is an error and if so do A and if not do B…
=If(IsError(VLOOKUP($A10,EY!$C$3:$G$33,2, FALSE)),If(IsError(VLOOKUP($A10,MY!$C$3:$G$33,2,FALSE)),If(IsError(VLOOKUP($A10,LY!$C$3:$G$33,2,FALSE)),”Not Found”,”LY”),”MY”),”EY”)
You’ll notice I added a $ in front of the A reference as your lookup cell column remains fixed if you are extending the formula to columns to the right. You may also wish to fix the lookup for the column to a set day of the week and remove the $ so that C$3:C$33 will become D$3:D$33 for the next day of the week if it follows on accordingly so that you can just extend the same formula to the right and have it adapt automatically.
ie. The column for Monday would have this formula:
=If(IsError(VLOOKUP($A10,EY!C$3:C$33,1, FALSE)),If(IsError(VLOOKUP($A10,MY!C$3:C$33,1,FALSE)),If(IsError(VLOOKUP($A10,LY!C$3:C$33,1,FALSE)),”Not Found”,”LY”),”MY”),”EY”)
And when extended to the right for Tuesday the range reference will auto change to D$3:D$33 and so forth for Wed - Fri.
