Match to nearest in range, then return value of a different cell.

I don't know about GoogleDocs, but the closest function is probably HLOOKUP, but some adjustments would need to be made...

First, HLOOKUP doesn't find the closest value, but it does find the value less than or equal to your key value. For example, using your table above, for a key value of 15, it find the 6.375 since it is less than 15 and the 19.125 cell is greater. Another example, a key value of 100 would find the 95.625.

Second, HLOOKUP must have the range of values as the top row of its table. So, you would have to put the 6.375, 19.125, etc. as the top row of your table. Then you tell it which row in the table you want the value to be.

Best to check out a webpage: https://support.office.com/en-us/art...5-e1ad662ed95f

Hi Pauley,

Thanks for your reply. If it's not possible to od this in one step, would it be possible in multiple? Something like this:

1. Row recording the index of the row of scores (eg, row 1 shows index, not just a number indicating position)
2. Column which searches which values for closest and returns closest value (eg 'search row 3. Return number from row 3 which is closest to (value in G5))
3. In the new column, for closest value, return index x

This might be less streamlined, but in this context that's acceptable. Could you please advise what, if any forumuals might achieve this?

Illustrated again for calrity:

PDO question 2.png


Thanks again

Someone this morning asked almost the exact same question https://www.excelforum.com/excel-for...from-data.html There data is vertical where yours is horizontal, but, otherwise, either strategy we suggested should work for your data as well.

There are instructions at the top of the page explaining how to attach your sample workbook. You won't yet be able to post a link to your sheet - you'll need a few more posts for that, but if you want to post it without the https bit, I can convert it to a link for you, however we prefer attachments here (obviously converted to Excel).

I did try this originally and got an error - it may have been a dyslexia issue when specifying file type, sorry. I'll try again to attach it to this post, as well as another pic.

Basically, I need a formula htat compares the values in N4 to the value range B4:B45, finds the closest match, and returns the relative value from C4:C45 into P4. I've made the backgrounds grey to illustrate.

Attachment 679865

As I mentioned, my current formula is =INDEX(C4:C45,MATCH(MIN(ABS(-C4:C45),ABS(-C4:C45),0))) and it says I'm missing na argument in it. Any help always appreciated!

- Liam.

The TODAY() element was crucial as it was finding the date closest to today. You need to replace TODAY() with the number you want to get closest to. You then had a parentheses error and I think you were pulling from the wrong range for your answer.
I think this is what you want for your O column: =INDEX($B$4:$B$45,MATCH(MIN(ABS(N4-$B$4:$B$45)),ABS(N4-$B$4:$B$45),0))
This will work for your P column: =INDEX($C$4:$C$45,MATCH(MIN(ABS(N4-$B$4:$B$45)),ABS(N4-$B$4:$B$45),0))

You need to enter these with control-shift-enter. You can then drag the formula down.

The formula suggested earlier for P4 does work in Google Sheets:

=index($C$4:$C$45,match(min(ABS(N4-$B$4:$B$45)),ABS(N4-$B$4:$B$45),0))