Index match with multiple criteria's (where one is a header)

[Andre1980]

Hello,

Can someone help me with this problem? I need to get the hours from one sheet to the other but the lookup has multiple criteria's.

I can not find the solution. I think Index match does the trick but I don't understand the formula. Maybe someone can help me so that I can see what it does.

Thanks Andre

[Glenn Kennedy]

In F2, copied across and down:

=IFERROR(INDEX(Data!$E$3:$AB$21,MATCH(Test!$A2,Data!$A$3:$A$21,0),MATCH(Test!F$1,Data!$E$2:$AB$2,0)),"")

You may beed to use ; instead of ,

[Andre1980]

Thanks Glenn it almost works.

I only get the hours from week 50 pasted under week 49

I put in this formula:
=IFERROR(INDEX(Data!$G$3:$BV$360;MATCH(Test!A2;Data!$B$3:$B$360;0);MATCH(Test!$F$1;Data!$F$2:$BV$2;0));"")

[Glenn Kennedy]

You'll need to explain... I see week 49 under week 49 in the sheet I posted.... unless I'm losing my marbles...

If you are talking about another sheet, that i have never seen.... then I can't see what I can't see....

[Glenn Kennedy]

You amended your post...

=IFERROR(INDEX(Data!$G$3:$BV$360;MATCH(Test!A2;Data!$B$3:$B$360;0);MATCH(Test!$F$1;Data!$F$2:$BV$2;0));"")

You are inconsistent in your ranges. Both should start from the same column. I can't tell which is correct, though!!

[Andre1980]

Yes you are right. Because I don't want to share private information I made an extra sheet where I replaced the names with co-worker 1 enz.

Let's try this...

[Glenn Kennedy]

Therte were a few other errors in your formula, as you did not transcribe the $ signs carefully enough....

=IFERROR(INDEX(Data!$F$3:$BV$360,MATCH(Test!$A2,Data!$B$3:$B$360,0),MATCH(Test!F$1,Data!$F$2:$BV$2,0)),"")

[Andre1980]

You are the best.

Thanks.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.