My Index cells reside in AN20:BP20
and contained the following data: -100 -90 -80 -70 -60 -50 -40 -30 -20 -10 -8 -7 -6 -5 -4 -3 -2 -1 0 10 20 30 40 50 60 70 80 90 100
The MATCH cell is BE18 and is a merged cell group of 3 cells: BE18:BI18
these cells currently contain a single calculated data point of -32%
The RESULT cell is AM22 and contains the following: =INDEX(AN20:BP20,MATCH(MIN(ABS(AN20:BP20-BE18)),ABS(AN20:BP20-BE18),0))
This formula is entered as an array i.e. Shift/Control/Enter
My problem is the returned value is alway zero (0)?
I see one potential problem and that is the absolute qualification within the formula. I have tried removing the ABS's and the resulting formula just returns an error message.
As the data indicates I obviously need to be able to differentiate between positive and negative numbers.
Can someone please point out the error of my ways?
Thanks, Michael
Bookmarks