Creating a list from data without filtering

[dwillson]

Suppose you have a list of data like:

Col A Col B
Smith Yes
Thomas No
Wilson Yes
Johnson No
Fox No
Stride Yes
Gall Yes
Lackett Yes
Shope No
Robinson Yes

I want to lookup column B and find all instances of "No" and return corresponding value from column A, so the output would look like:

Thomas
Johnson
Fox
Shope

[NBVC]

Try:

=IFERROR(INDEX(A1:A100,SMALL(IF(B1:B100="No",ROW(A1:A100)-ROW(A1)+1),ROWS($A$1:$A1))),"")

adjust ranges to suit and then confirm with CTRL+SHIFT+ENTER not just ENTER and copy down as far as you need.

[dwillson]

Thank you. Worked perfectly! Need to learn more about CNTRL-SHIFT-ENTER.

[NBVC]

You can learn a bit here:

Array Formulas

and

Array Formulas