Index Match Multiple Criteria with Tolerance

[adrfredcdy]

Hi, I'm having trouble this one out.

Normally I would just use {=INDEX(A:A,MATCH(J4&K4,D:D&E:E,0))} to return the value I'm looking for to cells at column N.
However, values from J and K aren't exactly equal to values from columns D and E. They have about a less than 1% difference.
Any way to still make excel return a value if the criteria checks are within a given tolerance? Say 1% in this case. If it can't find a value within 1%, it just returns a #N/A or some error.

I've been fiddling with regular excel functions, but i figured maybe there's an answer using VBA.

First time asking here so please forgive me

Capture.JPG

[XLent]

Before committing to a VBA approach you might try the following formula (not an Array) ?

Please Login or Register  to view this content.

you may need to decide if you want to give priority to closest match as opposed to the above which will (I think) return first label found where D & E are both within the 1% of J & K respectively.

[adrfredcdy]

The formula works! Thank you so much XLent! Spent the whole afternoon trying to figure this thing out. Thanks thanks thanks!
I'll go ahead and study the formula so I know how it works. Thank you!

[XLent]

Glad it helped.

In essence the calculation works by:

1. dividing the row number by 2 booleans, the first Boolean being whether or not "X" is within threshold, the second Boolean being whether or not "Y" is within threshold
at this point, given the Boolean coercion (via Division) your resulting array of values can only be either a) row # (both thresholds met) or b) #DIV/0! (one or more thresholds not met)

2. using the AGGREGATE function we tell it to apply SMALL (15) function against values generated by above, to ignore Errors (6), and to return the smallest value (final parameter [1] being k)

we then apply the result of step 2 to the INDEX against Column B to return the associated label.

the IFERROR will return #N/A (as requested) if there are no valid results found.

Hope that helps clear things up a little.

[adrfredcdy]

Never would have thought of that ) thanks!

[Zafer123]

Hello, I am using your formula with slight mods:

It looks up a value in a list within a tolerance:

=IFERROR(INDEX(listC1,AGGREGATE(15,6,ROW(listC1)/(ABS(ROUND(1-listC1/C27,2))<=Tolerance),1)),NA())

C27 is the value to lookup, and there is an exact match in List1C.

It works great, unless I put a decimal number less than 1 in the tolerance, like .01.

Any help is appreciated.

[Zafer123]

Zero also fail;s

[Zafer123]

I see now that the tolerance is a percent.

[GeoffW283]

Comment deleted. Discussion continued in the following thread.

https://www.excelforum.com/excel-for...ml#post5599277