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

 

[Cleveranchors]

PS. I've made the Doc editable should anyone realise a quick fix

 

[AhoyNC]

See if this works for you.

It's an array formula and must be entered with CTRL-SHIFT-ENTER.

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		0
4	Manager	Person 12	Project 1		0
5	Manager	Person 2	Project 1		1.5
6	Exec	Person 3	Project 1		3
7	Exec	Person 21	Project 1		0
8	CEO	Person 11	Project 1		0
9	Temp	Person 1	Project 1		2.55
10	Temp	Person 13	Project 1		0
11	Temp	Person 14	Project 1		0
12	Exec	Person 4	Project 1		0
13	Exec	Person 5	Project 1		0
14	Exec	Person 7	Project 1		0
15	HR	Person 22	Project 1		0
16	Exec	Person 8	Project 1		0
17
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 9	Project 12	3.82
12		Person 10	Project 27	13.62
13		Person 10	Project 28	8.5
14		Person 1	Project 27	15.25
15		Person 11	Project 27	18.6
16		Person 11	Project 28	4
17		Person 12	Project 27	2.33
18		Person 13	Project 27	4.58
19		Person 14	Project 27	20.5
20		Person 5	Project 27	8.33
21		Person 15	Project 27	28
22		Person 6	Project 27	0.55
23		Person 16	Project 27	8
24		Person 8	Project 27	13.83
25
timesheet

 

[Cleveranchors]

Wow that was the fastest response ever! Ok - I'll have a play wit this now (really would like to nail this tonight!)

How can I thank you for this?

 

[Cleveranchors]

@AhoyNC
Thanks for your input - looks like you've got it working absolutely spot on! Presently I am getting no values across all cells from the IFERROR despite creating the array with Ctrl+shift+enter - I've tried increasing the range of values (ie. instead of $B$24, using $B$500 [so on and so forth] )

Any inklings? I'm having a bash at it, no luck yet (really need to brush up in these areas!)

 

[AhoyNC]

Not sure what you mean by "getting no values across all cells" is everything returning a 0?
The IFERROR is just used to return a 0 if no match is found.
You are right in that you need to adjust the cell references to fit your data.

 

[Cleveranchors]

Yes precisely that - it (IFERROR) returns a '0' for everything despite changing the cell references, I have cross referenced the data and it should match in many instances...hmmm I'm sure it's most likely something quite obvious.
I've been trying in both the GDoc sheet and in Excel 2010.
I'll keep at it anyhow's

 

[AhoyNC]

Not sure about GDoc but in Excel check the following.
=IFERROR(INDEX(timesheet!$D$2:$D$24,MATCH($B3&$C3,timesheet!$B$2:$B$24&timesheet!$C$2:$C$24,0)),0)

In the timesheet!$D$2:$D$24 change to your range of hours and make sure the range is locked.
timesheet!$B$2:$B$24&timesheet!$C$2:$C$24 change to your range and make sure it's locked.
MATCH($B3&$C3 this range should be from your schedule sheet. The columns should be locked, but not the rows as you copy formula down it should change to $B4&$C4 etc.

 

[Cleveranchors]

Thanks for elaborating further, I can confirm that MATCH($B4&$C4 are the only parts of the formula that change when it's copied down. All I've done is extended the ranges on:

timesheet!$D$2:$D$24
timesheet!$B$2:$B$24&timesheet!$C$2:$C$24

to

timesheet!$D$2:$D$63
timesheet!$B$2:$B$63&timesheet!$C$2:$C$63

to match the data

Everything seems correct - I wonder if it has something to do with rearming the trial version on Microsoft Excel Home & Business 2010 - maybe the extended license gets limited? It's the only thing I can think of...flumoxed otherwise - everything is as you've detailed

As a gesture of thanks I'm tweeting out your site, you've spent a lot of time with me tonight and I appreciate that

 

[Cleveranchors]

(plse delete this edited reply mods) premature conclusion