I have some code that runs off data in increments. If the code encounters an error it will enter #N/A as the value in the range. This is so I can still plot a graph of the results ignoring the errors.
I then want to pull out the closest match to a stated number from the range.
For example if my range is #N/A , 3 , 10 , 15 , 18 and I want to pull out the closest match to 9 then it would give me 10. Using the =INDEX(A1:A5,MATCH(B1,A1:A5,-1),1) will give me 10 but if the number I wanted was 4 I'd want it to give me 3. And if the number was 2 I'd want it to give me 3, or if it was over 18 I'd want it to give me 18 etc.
I've tried combining the index match function with looking for greater than or less than values but in some instances it won't work. I've attached an example where I have a target of 35.9, and instead of returning 38.27 as the closest match, it is returning the largest number in the range.
Hope I've explained my requirements well enough.
Bookmarks