1

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.

Improve this question
4
  • 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, 2016 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, 2016 at 12:44
  • Standard VLOOKUP could have three or four arguments. How many did you use?
    – Rubén ♦
    Oct 14, 2016 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, 2016 at 15:20

1 Answer 1

Reset to default
1 
=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)
Improve this answer
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, 2016 at 21:49
  • @Danny: Add the above findings to your question as part of your progress to find the solution.
    – Rubén ♦
    Oct 14, 2016 at 22:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.