Index/Match with multiple criteria, but one is exact and other is largest without going over

[mst3k4L]

I have a table that looks like the one below with the dates sorted in descending order.

Person	Date	Number
Jose	1/1/2014	98
Rosa	5/14/14	47
Jose	6/1/14	3
Rosa	2/1/15	456
Jose	12/5/15	3
Rosa	5/2/16	-10

I want to enter a name and date and have excel search the table for the exact name and the closest date without going over, and then provide the answer in the "Number" column. For example, entering "Rosa" and "6/2/14" will result in "47", while "Jose" and "6/2/14" will be "3".

I'm assuming it's some combination of Index & Match. Feel free to use something else, but please void VBA if possible. I can resort the table as needed. Thanks in advance!

 

[XLearner]

Hi,

Assuming Names in column A, Dates in column B, Number in column C (Titles in row 1) + NameInput in E1 and DateInput in E2. Where you want the result:

=INDEX($A$2:$C$101, MATCH(MIN(IF($A$2:$A$101=$E$1,ABS($B$2:$B$101-$E$2))),
                          IF($A$2:$A$101=$E$1,ABS($B$2:$B$101-$E$2)),0), 3)

validate with Ctrl+Shift+Enter (array formula)

Regards
XLearner

 

[mst3k4L]

Hi,

Assuming Names in column A, Dates in column B, Number in column C (Titles in row 1) + NameInput in E1 and DateInput in E2. Where you want the result:

=INDEX($A$2:$C$101, MATCH(MIN(IF($A$2:$A$101=$E$1,ABS($B$2:$B$101-$E$2))),
                          IF($A$2:$A$101=$E$1,ABS($B$2:$B$101-$E$2)),0), 3)

validate with Ctrl+Shift+Enter (array formula)

Regards
XLearner

Thanks for the quick response. Unfortunately I ran into a problem. The code appears to be looking at the closest date in the array, regardless of if it is greater than or less the entered date. This is probably because of the MIN(ABS()) command which appears to be minimizing the absolute difference.

 

[XLearner]

Are you saying I misunderstood "the closest date"? Are you looking for the closest to TODAY?

 

[mst3k4L]

Are you saying I misunderstood "the closest date"? Are you looking for the closest to TODAY?

I need to closest date that matches the criteria in the array without the date being greater than the date in the array. So if a person enters "Rosa", they can enter any date from "5/14/14" to "1/31/15" and the result will be 47. This is because these dates are greater than or equal to 5/14/14, but less than 2/01/15. Does that help?

 

[XLearner]

Don't want to waste your time... I really don't get it. Read and re-read your last reply several times but I still don't get the idea. Sorry about that

 

[XLearner]

Could you try:

=INDEX($A$2:$C$101, MATCH(LARGE(($A$2:$A$101=$E$1)*($B$2:$B$101<=$E$2)*($B$2:$B$101),1),
                          ($A$2:$A$101=$E$1)*($B$2:$B$101<=$E$2)*($B$2:$B$101), 0), 3)

validate with Ctrl+Shift+Enter

 

[mst3k4L]

Don't want to waste your time... I really don't get it. Read and re-read your last reply several times but I still don't get the idea. Sorry about that

Sorry for my poor explanations, but what if I did it in code? Instead of only having 1 conditional statement (Person = "Rosa"), I need 2 conditional statements (Person = "Rosa" & Date >= the date next to Rosa). The below code doesn't work, but it illustrates what I am trying to accomplish. I underlined the conditional statements.

=INDEX($A$2:$C$101, MATCH(MIN(IF([U]AND($A$2:$A$101=$E$1,$E$2>=$B$2:$B$101)[/U],ABS($B$2:$B$101-$E$2))),
                          IF([U]AND($A$2:$A$101=$E$1,$E$2>=$B$2:$B$101)[/U],ABS($B$2:$B$101-$E$2)),0), 3)

 

[XLearner]

Sorry for my poor explanations

Don't be, it's not easy and this might be me as well...
Have you tried my last proposal? I think it does what you expect

 

[mst3k4L]

Could you try:

=INDEX($A$2:$C$101, MATCH(LARGE(($A$2:$A$101=$E$1)*($B$2:$B$101<=$E$2)*($B$2:$B$101),1),
                          ($A$2:$A$101=$E$1)*($B$2:$B$101<=$E$2)*($B$2:$B$101), 0), 3)

validate with Ctrl+Shift+Enter

This actually works! The only problem I am having is that sometimes the date entered is greater than any other date with that criteria, so it just grabs the first data point in the table. For example, entering "Jose" and "1/1/16" should give an answer of "3", but it instead gives "98" (first entry in table). Any thoughts?