0

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)

Improve this question

1 Answer 1

Reset to default
2

Use FILTER:

=FILTER(A:B,(C:C="Team A")*(D:D=TRUE))
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

nice - also, =FILTER(A:B,(C:C="Team A")*(D:D)), right?
@markfitzpatrick yes, I just find it easier when helping others to explicitly set it, it avoids a misunderstanding.
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 it means AND if we wanted OR it would be +
Change the first reference to the column you want output.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.