Create List

[michael.a7]

I have the following data in columns A & B


Arabic 3
Chinese 4
English 2
French 1
German 3
Italian 1
Hebrew 4
Russion 1
Spanish 3

I would like to create a list in column C, that lists all the language
where the value is 3 - so column C should look like this:

Arabic
German
Spanish

Is there a formula that can do this?

Any help would be appreciated.

Thanks,
Michae

 

[Bob Phillips]

Select C1:C20, and enter this formula in the formula bar

=IF(ISERROR(SMALL(IF($B$1:$B$20=3,ROW($A1:$A20),""),ROW($A1:$A20))),"",
INDEX($A$1:$A$20,SMALL(IF($B1:$B20=3,ROW($A1:$A20),""),ROW($A1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[michael.a7]

Many Thanks :