Return Position of First Value Within X% Range of/Specific Margin of Error of Lookup Value

Hi all,

I am trying to make Excel return the position of the first value in a string of numbers that closely matches a lookup value. I am currently using MATCH(MIN(ABS())) to return the position of the closest value in the string, but this is not the ideal solution.

I have attached a sample file. The values in the example subject string increase and then decrease (though they may behave differently in different scenarios outside of this example). I want it to return the closest value before it decreases. The formula I am using currently (D8) returns 16, while the goal would be to have it return 11 (specified in file).

I'm hoping there is a way I can make it return the first value that is within, say, 5%, or within 10, of the subject value (D3).

Thank you!

Hello.

Depending on the criterion you use to get the goal value, maybe you could try:
=MATCH(D3*1.02,D6:AD6,1)

Good luck!

kvsrinivasamurthy

After porting this over to my file, this appears to be working, except for certain instances. Sometimes negative value(s) in the string of numbers seems to be throwing it off, or if the peak number in the string is too far off from the lookup value. This results in it throwing an #N/A or simply displaying the incorrect result.

Can you explain briefly what the logic in this formula is? This formula is beyond my ability to interpret.

Thank you