2

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?

Ram
  • 3,092
  • 10
  • 40
  • 56
JJay
  • 23
  • 3

1 Answers1

3

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

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • ahh! yes, i forgot about it being an array formula only. great answer still, thanks. – JJay May 05 '15 at 14:54