"close fit" formula for an row of data

QuestionEdit

I have a list of securities and several pricing sources for each(across a row). I am looking for the closest price to match a price that has been given - this price can be positive or negative. I would also like to reference the source on the next column. I have come across a fair amount of solutions, but none worked optimally.

AnswerEdit

sort your data in ascending order and use Vlookup and in [Range Lookup] mention it as 1 instead of 0. you can use the Lookup function also for retrieving the result.

You can use ABS function to ignore the Positive and Negative sign.