# Return cell reference containing closest value

I'm trying to get a spreadsheet to tell me which cell(s) contain values closest to a value I provide. Without going into too much detail it's for a golf game I play, and I've done some data collection which is entered into five ranges (1w,2w etc.) What I want to do is to be able to enter a figure (for yardage), and also the wind conditions. With this information I can manually look at my data to find the best club and power to use in this situation, but I want my spreadsheet to do it for me.

So for example, I want the ball to land at 383y, and I have a -1 wind. Looking at the below, I know I'm going to have to hit a 2W at between 94-96%: The exact value `383` does not appear in my range, so I want to know the cell reference which contains the closest match (C28). I can then use this to discover which club (B24) and what power (B28).

I've tried looking at VLOOKUP but that won't do it:

``````=VLOOKUP (a1,range,2)
``````

The problem with that is if the value isn't listed, which most of the time it isn't, it will fail. I want to return the closest value, which VLOOKUP doesn't appear to do.

I suspect that MATCH may work, but I can't work out exactly how.

• Welcome to Web Applications. Please take the tour and checkout How to Ask. Besides including your research efforts, also add the formula that you used when you tried VLOOKUP. – Rubén Oct 14 '16 at 12:22
• I tried using a standard Vlookup, but found that unless there was an exact match then it wouldn't work. – Danny Oct 14 '16 at 12:44
• Standard VLOOKUP could have three or four arguments. How many did you use? – Rubén Oct 14 '16 at 12:47
• e.g. =VLOOKUP (a1,range,2) - the problem with that is if the value isn't listed, which most of the time it isn't, it will fail. I want to return the closest value, which VLOOKUP doesn't appear to do. – Danny Oct 14 '16 at 15:20

``````=VLOOKUP (a1,range,2)
``````

The problem with that is if the value isn't listed, which most of the time it isn't, it will fail. I want to return the closest value, which VLOOKUP doesn't appear to do

First, sort `range` in ascending order. You could do in place or use SORT

Try

``````=VLOOKUP(a1,SORT(range,1,TRUE),2)
``````
• Thanks for looking, but that returns the value, whereas what I need to know is the cell reference. Also, it does not return the value closest to the one I type in, rather the next value down in the range. So for example if my range is 415,414, 405, etc, and I lookup on 413, Vlookup returns the value 405. I would like it to find the cell containing 414, which is closer to my required value of 413. – Danny Oct 14 '16 at 21:49
• @Danny: Add the above findings to your question as part of your progress to find the solution. – Rubén Oct 14 '16 at 22:41