index and match for multiple columns and rows

chuibchan

New Member
Joined
Sep 13, 2014
Messages
8
I'm sure this topic has been covered already.... but I didn't even know how to search the forum for my question.....

Example of my data:

Name RG OT DT
John Shift 1123
John Shift 24
56
John Shift 3
9
87
Max Shift 1101112
Max Shift 2151413

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>



Etc....

I need to create an excel function (I assume index and match) so that in another sheet:

Job# Name Shift Type Rate #Hrs Labor$$
xxxxx John Shift 1 OT (1-15) 6 HrsxRate

If I manually enter in the Job Number, Name, Shift, Type & #hrs, the Rate (1-15) will spit out.

The function will need to search the name column and the shift column and spit out the correct row....

Any help would be much appreciated! Thank you.
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Secret Chimpanzee

Board Regular
Joined
Jun 21, 2012
Messages
141
You will need to add an additional column to your table to add the Job # and assuming your table starts in cell A1, this might help:

=INDEX($A$1:$E$6,MATCH(A8,$A$1:$A$6,0),MATCH(A1,$A$1:$E$1,0))

Note, A8 (in the first MATCH) should reference your first criteria being the Job # and A1 (of the second MATCH) should reference the second criteria.

You will need to replicate your existing table so that the Column Titles match the first table and replicate this formula in each cell of your second table.
 

chuibchan

New Member
Joined
Sep 13, 2014
Messages
8
You will need to add an additional column to your table to add the Job # and assuming your table starts in cell A1, this might help:

=INDEX($A$1:$E$6,MATCH(A8,$A$1:$A$6,0),MATCH(A1,$A$1:$E$1,0))

Note, A8 (in the first MATCH) should reference your first criteria being the Job # and A1 (of the second MATCH) should reference the second criteria.

You will need to replicate your existing table so that the Column Titles match the first table and replicate this formula in each cell of your second table.


Dear Secret Chimpanzee (Awesome id btw).

Your solution got me a little closer. I actually don't need Job# to be included in the criteria. The only criteria necessary is Name, Shift and RG/OT/DT. I think I can use your method, but will need to daisy chain another match function to account for Shift. I will continue playing around. But, thank you for getting me 1 step closer.
 

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
761
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi

Excel 2010
ABCDE
1NameShiftRGOTDT
2JohnShift 1123
3JohnShift 2456
4JohnShift 3987
5MaxShift 1101112
6MaxShift 2151413
Sheet2



Excel 2010
ABCDE
10Job NumberNameShiftTypeRate
11not neededJohnShift 2OT5
Sheet2


formula in cell E11
Code:
=INDEX($C$2:$E$6,MATCH(B11&C11,$A$2:$A$6&B2:B6,0),MATCH(D11,$C$1:$E$1,0))

enter with Ctrl + Shift + Enter, (not just Enter)

Paul.
 

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
761
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi (again)
just added the $ sign to one of the ranges as below

Code:
=INDEX($C$2:$E$6,MATCH(B11&C11,$A$2:$A$6&$B$2:$B$6,0),MATCH(D11,$C$1:$E$1,0))

Paul.
 

chuibchan

New Member
Joined
Sep 13, 2014
Messages
8
Hi (again)
just added the $ sign to one of the ranges as below

Code:
=INDEX($C$2:$E$6,MATCH(B11&C11,$A$2:$A$6&$B$2:$B$6,0),MATCH(D11,$C$1:$E$1,0))

Paul.

Excellent!!!! Thank you. That works perfect! I did not realize & worked in equations.


Many thanks Paul and Secret Chimpanzee
 

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
761
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I did not realize & worked in equations.

You're welcome.
Yeah, I only recently learned that concatenation could be used with index match (thanks to an earlier thread)

Paul.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,951
Messages
5,834,538
Members
430,295
Latest member
amdis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top