look-up "1" and return a text string

Hello All,

I am new to the forum and quite new to more complex functions in Excel. I have the following sheet:

col1..col2

bmo..3
amd..1
dmq..2
smp..4
epm..1

I want to lookup the value "1" in col2 and return all the text strings in col1 that match.  So in this instance I would have the result "amd, epm"

Is this something I could achieve with a vlookup?

Thanks in advance,

Ben

Add this ARRAY formula to a cell

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

and copy down as far as you might need.

Hi Bob,

Thanks very much for the quick reply.

I have exchanged the values B1:B20 for D2:D185 for the column holding my integer values and exchanged A1:A20 for A2:A185 which is the range holding my names.

I have put the formula in D187 and copied down 185 cells.  However, they all come-up blank.

Any thoughts?

Thanks again,

Ben

Did you array-enter the formula, that is hit Ctrl-Shift-Enter after inputting the formula, not just Enter.

I have now!  Thanks.

That has improved the situation as the function is now return names, however not the correct number of names and not the right names.

It is returning the names below the correct row.

Thanks for all your help, I'll have a go at dissecting the formula and seeing where the problem lies.

Ok,

Changing A2:A185 to A1:A185 has now returned the correct names.

Dragging the array function down gives the correct amount of names - got it!!

Thanks a lot