Forum Discussion

devoostaff's avatar
devoostaff
Copper Contributor
May 11, 2025

How can I return one unique match

Hello

I am trying to match participant data for work, based on multiple criteria, and I want it to return me an ID number that has not yet been matched.

 

So far I understand that me using the following equation will only return me the first match =INDEX($A$478:$A$9549,MATCH(1,(C2=$C$478:$C$9549)*(D2=$D$478:$D$9549)*(E2=$E$478:$E$9549),0))

Whereas using =UNIQUE(FILTER($A$478:$A$9549,(C2=$C$478:$C$9549)*(D2=$D$478:$D$9549)*(E2=$E$478:$E$9549))) will return me all the unique matches for one ID

 

However, I just want to match up each ID (based on matching three criteria above) with another ID that has not yet been matched. If there is no unique, not yet used, match, would it just be able to return N/A?

 

Thank you for any help you can provide!

 

excel
Excel for web
Excel on Mac
Formulas and Functions
office 365

2 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 12, 2025
    =IFERROR(CHOOSEROWS(FILTER($H$2:$H$32,(C2=$I$2:$I$32)*(D2=$J$2:$J$32)*(E2=$K$2:$K$32)),COUNTIFS($C$2:$C2,C2,$D$2:$D2,D2,$E$2:$E2,E2)),NA())

    This is a small scale example which returns the intended result if i correctly understand the question. The ranges of the formula can be adjusted according to your requirement.

    I've highlighted the recurring combinations from columns C, D and E in the screenshot along with their occurrences in columns I, J and K.

Resources