How to find the closest number in range

Hi

I have a range of numbers 560, 800, 976, 1012 and I need to find which one is the closest to 1005. I know that it is 1012, but I need to find it with a proper function.

I've tried vlookup and other functions, but I guess something is missing.

Any help would be appreciated, thanks!

Do you mean the closest either + or - ?
LIke if the number was say 985, would the closest then be 976 ?

The closest in general, I guess where the range between 2 numbers is the smallest.

For example if we had the number 995 and range of 976 and 998 then the one that we are looking for is 998.

I gave a poor example, I since edited my post.
If it was 985, would the closest then be 976.

But I think you do mean either higher or lower..

Try this array formula entered with CTRL + SHIFT + ENTER

=INDEX($A$1:$A$4,MATCH(MIN(ABS(C1-$A$1:$A$4)),ABS(C1-$A$1:$A$4),0))

A1:A4 is your range, C1 is the number of interest.

Doesn't seem to be working

Yes, I used it the first time. I've tried 3 more times now, says an error. Not sure what to do :D

What error?

Post the exact formula you tried (I just made up ranges for my example, your ranges are probably different)

Might be best if you could attach a sample book

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Post a file: I have just tested the formula and it works OK.

Thank you, works now. I opened up a new excel worksheet and everything was fine.

Thanks!

You're welcome.