index match

[5h1l]

Hi Guys.

In the attached you have two sheets i use:

1. people & hours
2. summary

im trying to write an index match statement in the summary sheet in cell G11 which looks at the date in A11 and looks at the room number in either b7 or d7 and match these conditions to date and room number in the people & hours sheet and return the value.

currently i have the following array formula and cannot work out what i need to change:

=INDEX('People&Hours'!I3:I16,MATCH(D7,'People&Hours'!H3:H16,0),MATCH(MONTH(A11),'People&Hours'!G3:G16,0))

Then i need to replicate this in H11 for total number of hours.

Please help, thanks

5h1l

[FDibbins]

in your 2nd match, you are looking for a month number (1-12) in a column full of actual dates. Perhaps use =MONTH(G3) in column O sheet1 and then use that in the match?

[5h1l]

i took that formula from a previous file, and assumed i needed it. i dont need to match by month, i need to match by whole date.

[FDibbins]

ok cool, then just remove the month() part...leave the cell ref though

[popipipo]

You can't use 'index' this way because:
The first 'match formula' gives as result a rownumber youre looking for
The second gives a column number.

People&Hours'!I3:I16 is not a square so it won't work

[5h1l]

i left it like:=INDEX('People&Hours'!I3:I16,MATCH(D7,'People&Hours'!H3:H16,0),MATCH(A11,'People&Hours'!G3:G16,0))

and now its returning #ref....

[FDibbins]

ooops good catch pop...

try this instead...

on sheet1 add a helper column (you can put it anywhere and then hide it if you want) I used O

in the helper, copy this down...
=G3&H3

then change the formula in summary to this...
=INDEX('People&Hours'!I3:I16,MATCH(A11&D7,'People&Hours'!O3:O16,0),1)

[5h1l]

is there a special way i need to paste the formula. i have tried using auto fill to insert the formula below and its not working.


I have even tried to freeze certain parts of the formula to freeze the cells and now have: =INDEX('People&Hours'!I$3:I$16,MATCH(A12&$D$7,'People&Hours'!$O$3:$O$16,0),1)

But this doesnt work.

If someone has a solution without putting in the helper, that would be great.

Thanks

5h1l

[san671]

Hey, you need extra helper column cause you have three dates that are exactly same. You need extra column that makes them to differ from each other.
Thats why you need to add up room number and date.

[5h1l]

OK i have the formula working however when i try to copy the formula down using autofill it doesnt work.

Does each formula have to be pasted as an array formula using shift+cntrl+enter?

Thanks

5h1l

[san671]

Hey
Use this
=INDEX('People&Hours'!I:I;MATCH(A11&$D$7;'People&Hours'!O:O;0);0)

[5h1l]

Hi San

This formula has an error in it and doesnt work.