Index Match Rows and Columns

Azira

New Member
Joined
May 10, 2012
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
Okay I'll try to explain this the best I can...

I have table with Color, Job, and Many Passes. The Color and Job will never be duplicate (for instance there will never be Red and M 1 on two different rows)
The Pass dates are only the start dates and only go until the next Pass start date.

So using the example below, cell D14 should have the correct pass for the associated color, job and date. In this case Red and M1 on 8/31/2016 should yield Pass3.


Excel 2013
ABCDEFG
1ColorJobPass 1Pass 2Pass 3Pass 4Pass 5
2BlackM 08/10/2016
3GreenM 28/9/2016
4IndigoM 39/28/2016
5OrangeM 27/24/20178/7/20168/15/20169/5/20169/11/2016
6RedM 17/24/20178/7/20168/15/20169/5/20169/11/2016
7RedM 27/24/20168/10/20169/7/20169/29/201611/3/2016
8RedM 06/27/20167/28/20168/25/2016
9VioletM 110/11/201611/15/201612/20/20161/24/2017
10WhiteM 010/18/201611/21/201612/28/20161/25/2017
11YellowM 310/25/201611/28/20161/4/20172/2/2017
12
13ColorJobDatePass
14RedM 18/31/2016
Sheet1


Thanks for any help. I'm usually very good at getting stuff like this done, but this time it's really sending me for a loop.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Maybe...

Array formula in D14
=INDEX(C$1:G$1,MATCH(C14,INDEX(C$2:G$11,MATCH(1,IF(A$2:A$11=A14,IF(B$2:B$11=B14,1)),0),0)))
Ctrl+Shift+Enter

Hope this helps

M.
 
Upvote 0
Solution
Maybe...

Array formula in D14
=INDEX(C$1:G$1,MATCH(C14,INDEX(C$2:G$11,MATCH(1,IF(A$2:A$11=A14,IF(B$2:B$11=B14,1)),0),0)))
Ctrl+Shift+Enter

Hope this helps

M.

It worked beautifully.

I am astounded. Nesting Index and Match... very cool indeed. Personally I've never used a match value other than zero. So much of what I do relies on being an exact match, I never considered it.

I've learned just a little bit more about Excel.
Thank you.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top