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