Index Match Closest Value without Going Over

[seans81]

Hello,

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

I have a workbook with 2 worksheets. The first worksheet (sheet1) 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 product they ordered from column C. I wrote an index match function to try and accomplish this but I'm having an issue.

The formula works, but I need it to find the closest date value without going over the date in column A of Sheet1. Currently since its using abs function, it finds the closest value, even if its above the value.


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

Any help is appreciated!

 

[jasonb75]

Duplicate of

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

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

www.mrexcel.com

 

[Peter_SSs]

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Guidelines.

Any bumps, clarifications, or follow-ups should be posted to the linked thread.