Excel formula returning multiple values according to two criterias-CodePudding

I am looking for a formula (without VBA) that could provide the list of Location (Column F) where there is people with the same Name (Column G) and Birthday year (Column H), as an example.

I tried INDEX MATCH combined with COUNTIF or VLOOKUP but I either have only the first Location matching the criterias in the list, or an error.

CodePudding user response：

Unique Column Cells With Two-Column Duplicates

=LET(rCol,F2:F16,uCol1,G2:G16,uCol2,H2:H16,
    uAll,uCol1&","&uCol2,uOnce,UNIQUE(uAll,,1),rBol,ISERROR(XMATCH(uAll,uOnce)),
UNIQUE(FILTER(rCol,rBol)))

CodePudding user response：

You can create a column as unique key for each people in the first table. Maybe using some formula similar to this:

 =G2&H2

(This is to create a cell based on values from columns Name & Birthday) Drag to apply for all cells in this column

Create a same column in the table for Location, then now you can use the VLOOKUP or INDEX-MATCH formula