Excel - How to Use a VLOOKUP Formula - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.