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

seans81

New Member
Joined
May 14, 2020
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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?
 
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
ABCDEF
1CompanyProductOrder Date
2Company 1Sprockets08/05/2020
3Company 1Stamps12/05/2020
4Company 2Stamps09/05/2020
5
6DateCompany NameProduct
711/05/2020Company 1Stamps
812/05/2020Company 2
Sheet15
Cell Formulas
RangeFormula
F7F7=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 :(
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
If this works, I'll find you and buy you a drink - will confirm after i try it out!
 
Upvote 0
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?
 
Upvote 0
The first formula that I posted does that (post 3) and you said it was wrong, I'm lost.
 
Upvote 0
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?
 
Upvote 0
See if this helps,
Book1
ABCDEF
1CompanyProductOrder Date
2Company 1Sprockets08/05/2020
3Company 1Stamps12/05/2020
4Company 2Stamps09/05/2020
5
6DateCompany NameProduct
711/05/2020Company 1Sprockets
812/05/2020Company 2Stamps
Sheet15
Cell Formulas
RangeFormula
F7:F8F7=IFERROR(LOOKUP($D7,$C$2:$C$4/($A$2:$A$4=$E7),$B$2:$B$4),"No match")
 
Upvote 0
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 :(
 
Upvote 0
IT WORKED WHEN I ENTERED IT AS AN ARRAY! THANK YOU SO MUCH!!!
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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