Nested Index, Match Array Problem

[CaileanUK]

I very much hope someone can assist.

I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.

The range containing all the data

[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cat 1[/TD]
[TD]January 1, 2014[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cat 2[/TD]
[TD]January 1, 2014[/TD]
[TD]Steve[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cat 3[/TD]
[TD]January 1, 2014[/TD]
[TD]Phil[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Cat 1[/TD]
[TD]July 1, 2014[/TD]
[TD]Andrew[/TD]
[/TR]
</tbody>[/TABLE]


The search values are

[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Cat 1[/TD]
[TD]January 15, 2014[/TD]
[TD]???????[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Cat 1[/TD]
[TD]February 15, 2104[/TD]
[TD]???????[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Cat 1[/TD]
[TD]August 15, 2014[/TD]
[TD]???????[/TD]
[/TR]
</tbody>[/TABLE]


I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.

For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4

The best try I had for the formula in C7 was

{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}

This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.

I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.

I very much appreciate any assistance you can offer.

Thanks in advance.

 

[Rukt]

Hi CaileanUK, try this array formula:

=INDEX($C$1:$C$4,MATCH($B7,IF((($A$1:$A$4=$A$7)),$B$1:$B$4,NA()),1))

If you use "Evaluate Formula" to step through it, you'll see that it tries to MATCH against a date array that contains #N/A wherever the Category is not the one specified. You'll want to make sure your dates aren't stored as text. This returns John, John and Andrew for me as you describe; please let me know if it doesn't work for you.

Cheers,
Rukt

 

[CaileanUK]

Rukt

I am impressed. I wouldn't have figured that out in a month of Sundays.

Greatly appreciated.

Thanks

Cailean Uk