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?

 

[jasonb75]

It doesn't need to be entered as an array in excel, but I think that with google, if it looks like an array it needs to be entered as one.