Excel: get subarray from column with multiple criteria

Question

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":

Therefore, my result table should look like this:

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)

score 2 · Accepted Answer · answered Jun 25 '21 at 16:44

2

Use FILTER:

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

answered Jun 25 '21 at 16:44

Scott Craner

148,073
10
49
81

nice - also, =FILTER(A:B,(C:C="Team A")*(D:D)), right? – mark fitzpatrick Jun 25 '21 at 16:54
@markfitzpatrick yes, I just find it easier when helping others to explicitly set it, it avoids a misunderstanding. – Scott Craner Jun 25 '21 at 16:56
Ah.... FILTER( )! Good stuff! Let me ask you this: what's the deal with the asterisk between the two statements in the second arg? C:C="Team A") ASTERISK (D:D=TRUE) What is it doing there? – Bob Jun 25 '21 at 17:20
@Bob it means `AND` if we wanted `OR` it would be `+` – Scott Craner Jun 25 '21 at 17:26
Change the first reference to the column you want output. – Scott Craner Jun 25 '21 at 17:27
Ah! Got it! Thanks! Wow, this function just made my day's job a breeze! – Bob Jun 25 '21 at 17:35

Excel: get subarray from column with multiple criteria

1 Answers1