How do I find a closest number in Excel and bring back a corresponding cell?

Question

Row 1 : YORK / LEEDS / WINDSOR / CHESHIRE

Row 2 : 10 / 8 / 6 / 9

So I want to find the closest match to York in the other data across each row, and bring back the title of the closest match.

So the answer i want to bring back for row 2 (York = 10) is "CHESHIRE" as 9 is the closest to 10.

Thank you!

What if one of the values was 11? – Gary's Student Nov 19 '13 at 13:02 — Gary's Student, Nov 19 '13 at 13:02

score 4 · Answer 1 · answered Nov 19 '13 at 13:18

4

If your data is in A1:D2 try this "array formula"

=INDEX(B$1:D$1,MATCH(MIN(ABS(A2-B2:D2)),ABS(A2-B2:D2),0))

confirmed with CTRL+SHIFT+ENTER

If 2 or more values are equally close then the match will be with the first one from the left.....

answered Nov 19 '13 at 13:18

barry houdini

score 2 · Answer 2 · edited May 23 '17 at 12:06

2

There are reasons for avoiding array formulas, see e.g.:

If you care about this, and prefer not to use array formulas, the following works.

=INDEX(B$1:D$1,MATCH(MIN(INDEX(ABS($B2:$D2-$A2),0)),INDEX(ABS($B2:$D2-$A2),0),0))

Credits to Brad and barry houdini, who helped solving this question.

edited May 23 '17 at 12:06

Community

answered Nov 19 '13 at 19:43

2 Answers2