Greetings all,

I need to find a closest match based on 6 criteria: same region, closest category, closest rate, closest date, closest age, and closest amount.

Tab 1 has 900 rows of items, and I need to find their closest match from tab 2, which contains 11,000 items. Tab 2 has been advanced-filtered down to 11k from an original 50k, based on ranges I created for each criteria (+/- 25%, +/- 2 years, etc). I now need the best match from the filtered list.

There is a match for all items in tab 1 found in tab 2, but there are multiple matches, and I need one unique match to each item from tab 2 to appear beside its match in tab 1. Also without one being repeated…

Based off of another posting, titled "Finding a closest match with more than one criteria", posted on 08-11-2011 by Jayana. I created a formula that includes INDEX, MATCH, MIN, IF, ABS; as follows...

Formula: copy to clipboard 
=INDEX('Tab 2'!$A$3:$A$195;MATCH(MIN(IF('Tab 2'!$A$3:$A$195<>"";IF('Tab 2'!$C$3:$C$195='Tab 1'!C3;ABS('Tab 2'!$D$3:$D$195-'Tab 1'!D3)+ABS('Tab 2'!$E$3:$E$195-'Tab 1'!E3)+ABS('Tab 2'!$F$3:$F$195-'Tab 1'!F3)+ABS('Tab 2'!$G$3:$G$195-'Tab 1'!G3)+ABS('Tab 2'!$H$3:$H$195-'Tab 1'!H3))));IF('Tab 2'!$A$3:$A$195<>"";IF('Tab 2'!$C$3:$C$195='Tab 1'!C3;ABS('Tab 2'!D$3:D$195-'Tab 1'!D3)+ABS('Tab 2'!$E$3:$E$195-'Tab 1'!E3)+ABS('Tab 2'!$F$3:$F$195-'Tab 1'!F3)+ABS('Tab 2'!$G$3:$G$195-'Tab 1'!G3)+ABS('Tab 2'!H$3:H$195-'Tab 1'!H3));0)))


Unfortunately this formula is not working out for me, even when doing the crtl-shift-enter for arrays. Most of the results are #N/As, when I know there is a match to be found for them all.

Another problem I encountered, is that #N/A would show up when I would include one particular criteria (age). But when I would remove it, then the formula would work (for that one row..). I checked the formatting, the data type, I copy & pasted formatting from working columns, I did CLEAN and TRIM. It didn’t help. I created an example sheet, with much less data available, and now all of a sudden it’s not a problem.. ? Any ideas why that would be ?

Is this criteria-work something for a formula-based answer, or VBA ? (which I have not used before..)


Any advice is much appreciated! Thanks