Index/Match with rows and columns as criteria

[wmjenner]

I am trying to use an index/match formula to determine the hours to be paid to an employee who works for multiple companies within our organization.

In the attached sample, I am trying to look up the hours in worksheet Jeb! by the company name (col. A), the employee name, and the hours worked each day of the week (row 3) and pull them into the worksheet Play. You can see my lame attempt in the formula bar.

Part of my problem is that I'm not sure how - or if you even can - to mix rows and columns as the lookup criteria.

Can someone help? I'm sure it's pretty straightforward if you know what you're doing! Thanks for any help!

[Pete_UK]

Put this array* formula in cell C4 of the Play sheet:

=IFERROR(INDEX(Jeb!$A$1:$I$104,MATCH(1,(Jeb!$A$1:$A$104=$A4)*(Jeb!$B$1:$B$104=$B4),0),MATCH(C$3,Jeb!$3:$3,0)),0)

* Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual Enter.

Format the cell to show one decimal place, then you can copy across and down as required.

Change the name in cells B4:B7 to see a different set of results.

Hope this helps.

Pete

[wmjenner]

Exactly what I was looking for. Thanks so much!

[Pete_UK]

You're welcome, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete