Index/Match Array to find closest date/time AND match secondary criteria

[seans81]

Hello,

I want to start by saying I am using ctr + shift + enter.

I have a workbook with 2 worksheets. The first workbook has dates in column A, company name in column H

The second worksheet is called "Reference' and it has company name in column B, product in column C, and most recent order date in column G

I'm trying to match the date in sheet 1 to the closest date in sheet 2 for each company and return the prodcut they ordered from column C. I wrote an index match function to try and accomplish this but I'm getting an error "Did not find value '0' evaluation.

The formula:

=INDEX(Reference!$C$2:$C$70,

MATCH(MIN(IF(ISNUMBER(SEARCH($H906,Reference!$B$2:$B$70)),ABS($A906-$G$2:$G$17))),

IF(ISNUMBER(SEARCH($H906,Reference!$B$2:$B$70)),ABS($A906-$G$2:$G$17)),0))

Any help?

 

[seans81]

I've done this on a single sheet for testing, but it will work the same on 2 sheets. Should work in excel 2010 or newer.

In the event of a tie (multiple entries on the closest date, or entries an equal number of days before and after) the formula will return the match closest to the top of the list.

Book1
	A	B	C	D	E	F
1	Company	Product	Order Date
2	Company 1	Sprockets	08/05/2020
3	Company 1	Stamps	12/05/2020
4	Company 2	Stamps	09/05/2020
5
6				Date	Company Name	Product
7				11/05/2020	Company 1	Stamps
8				12/05/2020	Company 2
Sheet15

Cell Formulas
Range	Formula
F7	F7	=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$4)/($A$2:$A$4=E7)/($C$2:$C$4=(D7+AGGREGATE(15,6,ABS($C$2:$C$4-D7)/($A$2:$A$4=E7),1)*{-1,1})),1))

Ok so I got this to work in Excel, but I have a new issue. When I upload to google sheets, it doesnt work. Apparently Google doesnt support the Aggregate function

 

[jasonb75]

This is the same formula without aggregate, this one needs to be array confirmed.

=INDEX(B:B,SMALL(IFERROR(ROW($B$2:$B$4)/($A$2:$A$4=E7)/($C$2:$C$4=(D7+SMALL(IFERROR(ABS($C$2:$C$4-D7)/($A$2:$A$4=E7),""),1)*{-1,1})),""),1))

No guarantees it will work in google sheets though.

 

[seans81]

If this works, I'll find you and buy you a drink - will confirm after i try it out!

 

[seans81]

This is the same formula without aggregate, this one needs to be array confirmed.

=INDEX(B:B,SMALL(IFERROR(ROW($B$2:$B$4)/($A$2:$A$4=E7)/($C$2:$C$4=(D7+SMALL(IFERROR(ABS($C$2:$C$4-D7)/($A$2:$A$4=E7),""),1)*{-1,1})),""),1))

No guarantees it will work in google sheets though.

So it worked, but its having the same logic issue as my formula. For example, if for example, the Date is newer than the order date, it should pick the product with the Order Date closest to the Date, but not greater than the Date. Right now it is picking the Date closest and not taking into account less than.

I can share the google sheet with you if that helps?

 

[jasonb75]

The first formula that I posted does that (post 3) and you said it was wrong, I'm lost.

 

[seans81]

The first formula that I posted does that (post 3) and you said it was wrong, I'm lost.

I didnt say it was wrong, it just returns 'No Match' for every row in the table. Maybe I'm not using it right?

 

[jasonb75]

See if this helps,

Book1
	A	B	C	D	E	F
1	Company	Product	Order Date
2	Company 1	Sprockets	08/05/2020
3	Company 1	Stamps	12/05/2020
4	Company 2	Stamps	09/05/2020
5
6				Date	Company Name	Product
7				11/05/2020	Company 1	Sprockets
8				12/05/2020	Company 2	Stamps
Sheet15

Cell Formulas
Range	Formula
F7:F8	F7	=IFERROR(LOOKUP($D7,$C$2:$C$4/($A$2:$A$4=$E7),$B$2:$B$4),"No match")

 

[seans81]

Thanks, that did help and I rewrote the formula to match my sheets, but still getting the same 'No Match' for all 923 rows. I'm working in google sheets now so not sure if that makes a difference, but I don't think it should as it recognizes all elements in the formula.

I'm not sure if the date formatting was an issue so I also tried it by converting the 2 dates into values, still no luck

 

[seans81]

does this need to be entered as an array?

 

[seans81]

IT WORKED WHEN I ENTERED IT AS AN ARRAY! THANK YOU SO MUCH!!!