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]

Sorry, fixed my code with the array issue, the one i posted above is an older version.

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

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

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

 

[jasonb75]

Best suggestion that I can provide without a visual example would be

=IFERROR(LOOKUP($A906,Reference!$G$2:$G$70/(Reference!$B$2:$B$70=$H906),Reference!$C$2:$C$70),"No match")

You don't need to you use ctrl shift enter with this formula.

 

[seans81]

Thanks for the response but this just goes to the error handler for all rows.

I tried to visualize it the best I can below. I need to populate column C in table 1 by using the company name and date from table 1.

Company name must match table 2 and the date must be the closest to date from table 1 without going over. Right now I am using an absolute function to subtract the 2 dates and see which is closer, but since company 1 shows up twice in table 2 and the absolute value of the 2 dates subtracted for row 2 is closer than the abs value of row 1, the formula would return 'stamps' instead of 'sprockets'

=ArrayFormula(INDEX(Reference!$C$2:$C$70,MATCH(MIN(IF($H2=Reference!$B$2:$B$70,abs($A2-Reference!$G$2:$G$70))),IF($H2=Reference!$B$2:$B$70,abs($A2-Reference!$G$2:$G$70)),0)))

	A	B	C
1	Date	Company Name	Product
2	05/11/2020	Company 1	Sprockets
3	05/12/2020	Company 2

	A	B	C
1	Company	Product	Order Date
2	Company 1	Sprockets	5/08/2020
3	Company 1	Stamps	5/12/2020
4	Company 2	Stamps	5/9/2020

 

[jasonb75]

I thought that I read that you wanted closest without going over.

There is another fairly recent question on the forum where the closest match either way was needed, I'll see if I can find it.

edit:- found it and don't think that the solutions we used there will be of any use here so I'll start looking at other ways.

 

[jasonb75]

Is your user profile showing the correct version of excel? There are functions in newer versions which will make the task significantly easier.

 

[seans81]

Yes - but I my have access to newer version. What is the function?

 

[jasonb75]

I was thinking MAXIFS and MINIFS, but may have found a way to do it without.

 

[seans81]

I was thinking MAXIFS and MINIFS, but may have found a way to do it without.

You'd be my saviour if you have! I've been stuck on this all day

 

[jasonb75]

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))