Search code examples
excelexcel-formulaarray-formulasexcel-match

What could this expression in Excel mean

Using INDEX and MATCH for lookups and came across an expression someone used in the form of:

=INDEX(*range used*, MATCH(MIN(ABS(data!E2-lookup!$L$5:$L$105)),ABS(data!E2-lookup!$L$5:$L$105),0))

lookup!$L$5:$L$105 is the value lookup table range. I know what its suppose to do but the data!E2-lookup!$L$5:$L$105 part does not make sense. How does this work?

Solution

  • This formula works only as an array formula.

    The MATCH part gets the position of that value in lookup!$L$5:$L$105 which is nearest to the value in data!E2. The INDEX part then gets the corresponding value in *range used*. In words of the formula: It matches that value in the array of differences ABS(data!E2-lookup!$L$5:$L$105) which is the smallest of those differences.

    Example:

    enter image description here

    Formula in F2 is:

    {=INDEX($A$2:$A$11,MATCH(MIN(ABS(E2-$B$2:$B$11)),ABS(E2-$B$2:$B$11),0))}

    Note, this is an array formula. Input it into the cell without the curly brackets and finish it with [Ctrl]+[Shift]+[Enter].