Question description:
This user has given permission to use the problem statement for this
blog.
I need help with a vlookup type formula that will take a given name and reference it against a list of names in columns D-O. then take a given date and reference it against dates in columns A10-A440, then return the cell or its contents at the intersection of those that column and row.
Solved by I. H. in 13 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/03/2018 - 06:49
Hello, I understand that you need a lookup that works with the column and rows as well.
Excelchat Expert
16/03/2018 - 06:50
I can see that you are writing a sample data. Thank you. That would help.
User
16/03/2018 - 06:50
yes when i give it a name and a date i want it to return the hours they are working that day
Excelchat Expert
16/03/2018 - 06:50
Okay, we can help you with that. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question.
User
16/03/2018 - 06:51
ok great
Excelchat Expert
16/03/2018 - 06:51
Where will you write the name and date?
User
16/03/2018 - 06:51
it is on a separate sheet
Excelchat Expert
16/03/2018 - 06:51
I created a Sheet. 2. Please write the name and date in there.
Excelchat Expert
16/03/2018 - 06:51
That way, the formula can be as close as possible to your original file.
User
16/03/2018 - 06:52
the formula i have been attempting looks like htis
User
16/03/2018 - 06:52
=IF(H$10="Vacant","",IF(ISBLANK('6 W Master'!$G$10),"",IF(CALC!$A$2='Working Copy 5 Week'!H$10,IF(CALC!$AD4=FALSE,'6 W Master'!G10,CALC!$AD4),IF(CALC!$A$44='Working Copy 5 Week'!H$10,IF(CALC!$AD46=FALSE,'6 W Master'!G10,CALC!$AD46),IF(CALC!$A$86='Working Copy 5 Week'!H$10,IF(CALC!$AD88=FALSE,'6 W Master'!G10,CALC!$AD88),IF(CALC!$A$128='Working Copy 5 Week'!H$10,IF(CALC!$AD130=FALSE,'6 W Master'!G10,CALC!$AD130),IF(CALC!$A$170='Working Copy 5 Week'!H$10,IF(CALC!$AD172=FALSE,'6 W Master'!G10,CALC!$AD172),IF(CALC!$A$212='Working Copy 5 Week'!H$10,IF(CALC!$AD214=FALSE,'6 W Master'!G10,CALC!$AD214),IF(CALC!$A$254='Working Copy 5 Week'!H$10,IF(CALC!$AD256=FALSE,'6 W Master'!G10,CALC!$AD256),IF(CALC!$A$296='Working Copy 5 Week'!H$10,IF(CALC!$AD298=FALSE,'6 W Master'!G10,CALC!$AD298),IF(CALC!$A$338='Working Copy 5 Week'!H$10,IF(CALC!$AD340=FALSE,'6 W Master'!G10,CALC!$AD340),IF(CALC!$A$380='Working Copy 5 Week'!H$10,IF(CALC!$AD382=FALSE,'6 W Master'!G10,CALC!$AD382),IF(CALC!$A$422='Working Copy 5 Week'!H$10,IF(CALC!$AD424=FALSE,'6 W Master'!G10,CALC!$AD424),IF(CALC!$A$464='Working Copy 5 Week'!H$10,IF(CALC!$AD466=FALSE,'6 W Master'!G10,CALC!$AD466)))))))))))))))
Excelchat Expert
16/03/2018 - 06:52
I think that's overly complicated but we'll see.
User
16/03/2018 - 06:52
it has a number of nested formulas but i think it is just to involved
Excelchat Expert
16/03/2018 - 06:53
Won't it be better if the example you have will have different time per day?
Excelchat Expert
16/03/2018 - 06:53
Like in your example, bob always has 7a-5p so it shouldn't matter what date?
User
16/03/2018 - 06:53
the times are spread through out he whole year.
User
16/03/2018 - 06:54
i am making a schedule workbook that will help me make monthly schedules for my business. we work shift work on rotating two week schedules.
User
16/03/2018 - 06:55
i made a yearly master schedule that shows everybodys shifts they work for th entire year. i then have a sheet that pulls out one month based on the dates i have given it.
User
16/03/2018 - 06:55
peopls shifts change every two weeks
Excelchat Expert
16/03/2018 - 06:55
The formula that you need is in C2.
Excelchat Expert
16/03/2018 - 06:56
Basically, it checks what row the date is and what column the name was found.
Excelchat Expert
16/03/2018 - 06:56
As long as they match then it will return the cell where they will intersect.
User
16/03/2018 - 06:58
ok so the index is the locator and the match function matches the name first then teh date?
Excelchat Expert
16/03/2018 - 06:58
That's right.
Excelchat Expert
16/03/2018 - 06:59
You can see how it works via row 3.
Excelchat Expert
16/03/2018 - 06:59
I wrote the standalone MATCH() formulas in there.
Excelchat Expert
16/03/2018 - 06:59
So the first 1 says to go to ROW4 of the index()
Excelchat Expert
16/03/2018 - 06:59
And then column 3.
Excelchat Expert
16/03/2018 - 07:00
And the index is : Sheet1!$A$1:$F$4
User
16/03/2018 - 07:00
ok i think that will work. awesome thanks
Excelchat Expert
16/03/2018 - 07:00
You are welcome. I'm glad I could help!
Excelchat Expert
16/03/2018 - 07:00
Would there be anything else that I can help you with regards to the original question?
User
16/03/2018 - 07:00
is there a way to save this where i can access it once this chat is over?
Excelchat Expert
16/03/2018 - 07:01
One moment.
Excelchat Expert
16/03/2018 - 07:01
Please download this file.
[Uploaded an Excel file]
Excelchat Expert
16/03/2018 - 07:01
Let me know once you've downloaded it.
User
16/03/2018 - 07:01
nevermind i found it. i really appreciate your help.
Excelchat Expert
16/03/2018 - 07:01
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating if you think I deserve it. :)
Excelchat Expert
16/03/2018 - 07:02
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of. :)
Excelchat Expert
16/03/2018 - 07:02
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User
16/03/2018 - 07:02
sounds good do i rate you hen its over?
Excelchat Expert
16/03/2018 - 07:02
Yes please. I'd really appreciate that.
User
16/03/2018 - 07:02
ok thanks have a great day
Excelchat Expert
16/03/2018 - 07:02
Thank you for contacting Got It Pro. Have an awesome day
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
