Return Unique Values based on certain cell & column

[edins425]

Hi, I'm hoping there's a way to display unique values from a column based on a certain cell and column. Looking at the attached image, I'd like to build a formula that lists all 'BSLs' in Column A (just once), based on what's displayed in cell H1. Ideally, all I'd have to do is type in any department name in H1, and columns A3:A23 will populate with a unique list of BSL's (column J), based on criteria in columns H and I. I'd like to avoid column A listing any BSL twice if possible.

 

[Fluff]

How about

=UNIQUE(FILTER(J2:J100,I2:I100=H1))

 

[edins425]

Thank you, this works! D

How about

=UNIQUE(FILTER(J2:J100,I2:I100=H1))

This works, thank you!! Is it possible to filter on more than one cell? For example, if in column K I had Operating and Capital, and only wanted it to return Operating, can that be added to this formula?

 

[Fluff]

How about

=UNIQUE(FILTER(J2:J100,(I2:I100=H1)*(K2:K100="Operating")))

 

[edins425]

How about

=UNIQUE(FILTER(J2:J100,I2:I100=H1))

Thank you, this works! D

This works, thank you!! Is it possible to filter on more than one cell? For example, if in column K I had Operating and Capital, and only wanted it to return Operating, can that be added to this formula?

I got it sorted, thanks again! I realized you can do and/or to add multiple criteria and it works great.

 

[Fluff]

You're welcome & thanks for the feedback.