Index Match Help: Multiple cell references (combined) - range lookups across tabs

[Cleveranchors]

I'm new here so good day all! Could do with a bit of assistance if anyone has time?
I have tried searching Google and these forums for an answer I could apply to the situation ...

Ie. Inside these threads:

• http://www.mrexcel.com/forum/excel-questions/785225-need-help-nested-concatenate-index-match.html

• http://www.mrexcel.com/forum/excel-...-match-return-column-two-drop-down-lists.html

• http://www.mrexcel.com/forum/excel-questions/785352-index-function-conditional-range.html

• http://www.mrexcel.com/forum/excel-questions/784813-index-matching-rows-condition-help-required.html

• http://www.mrexcel.com/forum/excel-questions/784848-index-match-multiple-criteria-array.html

...but I'm not quite getting the logic behind this Index match setup...despite spending a few hrs learning the definitions of each part of the formula structure...

The plan in theory is simple:
(Please see example docs below to get a better idea)

Reference 2 cells combined (ie. $B$3&$C$3 in 'schedule' tab and allow it's range to stretch the entirety og Cols B3:B5000+ & C3:C5000) Match these with 'timesheet' tab for the same range and pull back the corresponding '# hrs' values in 'timesheet' tab into 'schedule' in COL E.

Is INDEX / MATCH the correct function to use for these types of formulas please?

Hopefully these supporting references illustrate my plans further:

Pic 1: schedule tab
The idea is to pull the 'hrs' value from this tab using reference points / ranges

https://docs.google.com/file/d/0B7ZDtUDVoADYdnNfWUFhaTZJbWs/

Pic 2: timesheet tab
and input them in here for the corresponding values (&) in COLS B:C

https://docs.google.com/file/d/0B7ZDtUDVoADYQkxWd3pnamNidWM/

I've shared a link to a GDoc here to illustrate the workings and my point:

https://docs.google.com/spreadsheets/d/12YkFDAUKEYIZzPHdRj-shPP2LX7p8ZWf-3BBcJPzDws/

I really hope that someone here can help, I've been looking on Youtube and walking through many tutorials - I just aint getting it

Thanks in advance


~ CA

 

[AhoyNC]

Lets look at one more thing. Also chect the names of your sheets (make sure no spaces in the tab name).
Copy the example I have below into your Excel (you should be able to use copy paste).

I took out the IFERROR part.
Lets see if you get the same answer.
Person 12 has no match so the #NA.

Excel Workbook
	A	B	C	D	E	F
1	Position	Staff Member	Project	Scheduled Hrs	Hrs Spent
2	****	****	Project 1	60
3	Manager	Person 20	Project 1		8
4	Manager	Person 12	Project 1		#N/A
5	Manager	Person 2	Project 1		1.5
6	Exec	Person 3	Project 1		3
7
schedule

Excel Workbook
	A	B	C	D	E
1	Null	User	Project	Hours
2		Person 1	Project 1	2.55
3		Person 2	Project 1	1.5
4		Person 1	Project 2	3.63
5		Person 3	Project 1	3
6		Person 4	Project 12	18
7		Person 5	Project 12	0.75
8		Person 6	Project 12	2.73
9		Person 7	Project 12	14
10		Person 8	Project 12	13.5
11		Person 15	Project 27	28
12		Person 6	Project 27	0.55
13		Person 20	Project 1	8
14		Person 8	Project 27	13.83
15
timesheet