# Matching multiple criteria where one of the them needs to match a range or closest value

1. ## Matching multiple criteria where one of the them needs to match a range or closest value

Hi all!

I'm in dire need of help. =(

I have 2 tables:
Table 1: is what I need to be filled out. The column I need to fill out is the Standard Level.
Table 2: is my reference table.
The criteria that needs to matched are the Standard Title, Market Grade, and BillRate.
The pinch is that the BillRate on the Table 1 does not exactly match the billrate on table 2, it's either higher or lower, but that's acceptable, it just needs to be within +/-15% range or whatever is closest if it's beyond +/-15% [I know. It's confusing. =(].
It's easy enough using IndexMatch if the billrates match but the threshold is killing me. =(  Register To Reply

2. ## Re: Matching multiple criteria where one of the them needs to match a range or closest val

Hi

first attempt

=IFERROR(INDEX(Table2_Reference!B\$2:B\$226,AGGREGATE(15,6,ROW(\$2:\$226)-1/((Table2_Reference!A\$2:A\$226=A2)*(Table2_Reference!C\$2:C\$226=C2)*(ABS(Table2_Reference!D\$2:D\$226-D2)<=D2*15%)),1)),"")

Regards  Register To Reply

3. ## Re: Matching multiple criteria where one of the them needs to match a range or closest val Originally Posted by canapone Hi

first attempt

=IFERROR(INDEX(Table2_Reference!B\$2:B\$226,AGGREGATE(15,6,ROW(\$2:\$226)-1/((Table2_Reference!A\$2:A\$226=A2)*(Table2_Reference!C\$2:C\$226=C2)*(ABS(Table2_Reference!D\$2:D\$226-D2)<=D2*15%)),1)),"")

Regards
Hi canapone!

Thank you for this!

Question though, if we remove the 15% threshold and just go with the closest possible value, would this still work?

Thank you!  Register To Reply

4. ## Re: Matching multiple criteria where one of the them needs to match a range or closest val

Hi

try to substitute the segment <=D2*15% with

=AGGREGATE(15,6,Abs(Table2_Reference!\$D\$2:\$D\$226-D2)/((Table2_Reference!\$A\$2:\$A\$226=A2)*(Table2_Reference!\$C\$2:\$C\$226=C2)),1)

Regards  Register To Reply

5. ## Re: Matching multiple criteria where one of the them needs to match a range or closest val Originally Posted by canapone Hi

try to substitute the segment <=D2*15% with

=AGGREGATE(15,6,Abs(Table2_Reference!\$D\$2:\$D\$226-D2)/((Table2_Reference!\$A\$2:\$A\$226=A2)*(Table2_Reference!\$C\$2:\$C\$226=C2)),1)

Regards

I looked for some references and was able to simplify the formula and actually found something that worked.

Thank you so much for your help!!  Register To Reply