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

[Snoochieboochie12]

Hi,

Apologies I'm new with all this and not up on the lingo.

I am trying to find a code/formula that will return all values in column A that have "No" on the same row in Column N.

I've tried searching but the only formula I found that described what I wanted to do just keeps returning an error.

Any help would be much appreciated.

[bakerman]

First of all welcome to the forum and no need to apologize at all.
Secondly what must happen with the found values, are they to be highlighted or written to another range or ...
Maybe an examplefile with some data and the formula you tried might help clarify some things.

[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]

Yes, something like that

[Excel Fox]

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

[bakerman]

Macro based solution.
Select Columnheader in A1, set value to search in B1 then press button.