"close fit" formula for an row of data

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:14 PM GMT

Question

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.


Answer

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.

Advertisement

©2017 eLuminary LLC. All rights reserved.