How do I find a closest number in Excel and bring

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!

Answer 1

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

Answer 2

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

• A Microsoft page (look for "Disadvantages of using array formulas").

• This.

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.