Search code examples
excelexcel-formulauniqueexcel-365

Excel: get subarray from column with multiple criteria

I'm sorry I'm new to Excel, so I'm asking questions that I could probably Google if I new enough terminology to come up with a good search phrase.

Here's a simplification of my problem:

Using this table, I need to get the UIDs (and then the corresponding names, but that's easy) of every active player on "Team A":

source_table

Therefore, my result table should look like this:

result_data

The problem is unique-ness.

It's easy to find the UID using the conditions 'column C cells must have "Team A"' and 'column D must be TRUE"'... I find good old Arthur's UID, the first on the list.

Then I drag whatever formula down to the rest of the column, I find... Arthur's UID again (I've used VLOOKUP, INDEX/MATCH and XLOOKUP among others). I can't figure out how to get a result and then ignore that result after I've populated a cell with it.

How do I do this?

note I'm using Office 365 (in case there are non-backward-compatible formulas)

Solution

  • Use FILTER:

    =FILTER(A:B,(C:C="Team A")*(D:D=TRUE))