trying to extract unique values with a formula

[nmss18]

Hello,
I have a very large table in Sheet 1, in the following range A10:1:3068.
Column C has the company name, and there are multiple instances of the same company name in this column. (Columns E to I are part of a pivot table and I have various formulas in A thru D).

In column A I have a value indicating whether the company has 'exposure' (an internal meaning for our sales team). If the company has exposure, then the word exposure is indicated otherwise the cell is blank.

What I need to do is in Sheet 2, create a table that extracts a unique list of company names from column C only where it has the word 'Exposure' next to it in column A.

I would like this to be dynamic so if I change a value in the pivot table in sheet 1 the values in the table in sheet 2 will update accordingly.

I am figuring this is an index and match or perhaps a countifs but my head is stuffed.

Any ideas would be much appreciated.
Thanks,
nmss18

 

[Aladin Akyurek]

Thanks for the sample...

Let Sheet1, A1:E23, house the sample you provided.

Define Ivec using Formulas | Name Manager as referring to:

=ROW(Sheet1!$A$2:$A$23)-ROW(Sheet1!$A$2)+1

Let Sheet2, A:B, house the processing...

A2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$A$2:$A$23,SMALL(IF(FREQUENCY(IF(Sheet1!$A$2:$A$23<>"",
  MATCH(Sheet1!$A$2:$A$23,Sheet1!$A$2:$A$23,0)),Ivec),Ivec),ROWS(A$2:A2))),"")

B2, just enter and copy down:

=IF($A2="","",VLOOKUP($A2,Sheet1!$A$2:$B$23,2,0))

 

[KMacauley]

<Deleted Post>

 

[nmss18]

Wow. That worked. Thank you all very much.

 

[Aladin Akyurek]

Wow. That worked. Thank you all very much.

You are welcome.