Find the Closest Match in Excel using data from a Table

0
votes

I am trying to write a formula in Excel that will find the closest match in one column and return that value, so that it can be used in a SUMIFS formula. I have done some investigating and found that everyone points at this specific formula:

{=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0))}

The issue I am having though is I am trying to convert this to use data from a Table and have this so far:

{=INDEX(_CCD01[[#All],[Date]],MATCH(MIN(ABS(_CCD01[[#All],[Date]]-TODAY())),ABS(_CCD01[[#All],[Date]]-TODAY()),0))}

But Excel returns a #VALUE! error.

What the formula needs to do, is find the closest date in column _CCD01[[#All],[Date]] using TODAY() as the search criteria.

The file with the table and formula can be found here: Dropbox Read Only

Any help or thoughts will be appreciated.

Dave

1
excelformula
To let you know, using 0 on the third MATCH parameter, you are looking for an exact match (which btw is not the cause of your error itself) - JvdV
@JvdV The nature of the function is that there must be an exact match. The problem is in the MIN(ABS(…)) part of the formula. - Ron Rosenfeld

1 Answers

0
votes

This is an array formula.

=INDEX(_CCD01[Date],MATCH(MIN(ABS(_CCD01[Date]-TODAY())),ABS(_CCD01[Date]-TODAY()),0))

To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

The syntax you were using _CCD01[[#All],[Date]] will also return the header row of the column. There's no need for this, and it is the cause of your #VALUE! error.

  • The ABS function will return a #VALUE error (as the first element) in the array it returns when applied against the column since the first entry is text.
  • And then MIN arguments that are error values or text that cannot be translated into numbers cause errors.