Finding All Values In A Column That Corresponds To A Specific Text In Another Column

[Snoochieboochie12]

http://sdrv.ms/16eLLsn

Hi Bakerman,

What I am looking to do is have a box or column on a new sheet that returns all the Cell ID's in column A that do not have a 17th Edn Certificate (All that say no in column J).

I know how to search for a variable and return whatever constant, or how to count all the cells containing the constant, but not how to search a constant and find all variables associated with it if you catch my drift.

Unfortunately I can't find the one I tried, but it was an array formula. Obviously filtering can achieve what I want, but eventually I want to be able to do this across all columns to get an overview of what cell id's (sites in our business terms) are missing whatever component, so filtering is a tad time consuming.

Any help would be much appreciated

[Snoochieboochie12]

I suppose some kind of formula that returns the nth smallest value for all cells where J(rownumber) = no, where n increases by one with each successive formula applied, or something to that effect?

[Excel Fox]

Here's a formula based approach as you've hinted. Note that the formula has slowed down the workbook.