Picking the second value out of two results

[Hitch75]

Hi guys

Can someone help me solve this?

In the example attached, the red formula calculates which of the numbers in columns A-G has the closest value to the number given in the blue column.

Out of 6, 14 and 18, the closest one to the given number (12) is 14.

However, when two of the numbers are at the SAME distance to the given number, as in the last two rows, formula returns the first one of them, and i would like it to return the SECOND one.

So, i want the results of the last two rows to be 10 and 13.

Any ideas how to fix this?

Thanks!

[BlastRanger]

Hey Hitch
I'm curious why your formula got {} in beginning and the end of it.
{=INDEX(A1:G1,MATCH(MIN(ABS(A1:G1-H1)),ABS(A1:G1-H1),0))}

When I edit then enter, the result is #VALUE!

It's weird

[Hitch75]

It's done with ctrl+shift+enter

[BlastRanger]

Hi another question,

Please Login or Register  to view this content.

Another question why you want the last two row to take the high one, but the third row from the last you want 5 not 10?
5+2.5 = 7.5
10-2.5 = 7.5
and you prefer 5 is the closest? why not 10?

[Hitch75]

you're right, i want 10 there too, sorry!

[BlastRanger]

=INDEX(A4:G4,MATCH(MIN(ABS(A4:G4-H4)),ABS(A4:G4-H4),1)) (Follow by Ctrl+Shift+Enter)
Copy down
Is that what you try to get?

[Hitch75]

Yes, but why the last two rows (which i added to check the formula) don't work?
See the file...

[BlastRanger]

in this case use this instead :

Please Login or Register  to view this content.

[Hitch75]

Now it's perfect. Thanks!