Find nearest value in list

[danix]

I have a list of P10:T620. P column has values from 200 to -100. T column has the value I want to get.

How do I fetch the nearest value of, say, 100 from column P and then return the T column value of it?

If it was a vlookup it would look like this:
=vlookup(100;P10:S620;5;1)
obviously that doesn’t work because I don’t have a 100 in the T column. I have a 100.1 or a 99.9567.

This also feels very common question but search doesn’t find an answer. I found some excel tips from other sites but those did not work.

I have added the excel link and my attempt below:
https://www.extendoffice.com/documents/excel/1050-excel-find-closest-value.html
The spell I get looks like this:
=INDEX(P10:P620;MATCH(MIN(ABS(P10:P620-100));ABS(P10:P620-100);0))
which should give me the closest value near 100. But it gives 105.2342 which is not even close… And how would I use that anyway to get the vlookup value from the T column…

[Lupp]

What do you mean by “list of P10:T620”? That’s a range with 5 columns.
Did you consider if it is acceptable for you to get the result associated with one arbitrary occurrence (the one found) of probably many occurrences of the same “nearest value”?
Or: Why should this not be an issue in your case? Can you assure that any value being “nearest” in your column P is associated with the same result found in column T? Or can you assure that all the values in column P are unique?

[JohnSUN]

Perhaps you are looking for this formula?

{=INDEX(T10:T620;MATCH(MIN(ABS(P10:P620-100));ABS(P10:P620-100);0))}

Please make sure that this is an array formula, the input of the formula is completed with Ctrl + Shift + Enter

[danix]

Got it to work, thanks!