IF function that searches one column and lists the results in a paragraph

[utahmoose]

I am trying to automatically have Excel search a column for matching text and then compile all of the matches together. For example:

A	B
Blue	cat
Red	dog
Yellow	horse
Blue	goat
Green	dog
Blue	dog

In a single cell, I want it to search column A for a term and return the corresponding text in B. For example, if I were searching column A for "Blue", it would return "cat, goat, dog".

My thought was some sort of IF statement (=IF(A1:A6 = "Blue",....), but haven't got a clue as to how to get what I want.

Thanks!

[XOR LX]

Hi,

Are you insistent that the returns be in the same cell? This will require VBA (or else unfeasibly long fomulae). If, however, you would settle for each match being returned into its own cell (e.g. to the right of your search word) then this is straightforward.

Regards

[Pete_UK]

Will you always be looking for "Blue", or might you want to specify a different keyword? If you do then it will be easier to use a cell to enter the keyword, so which cell are you likely to use?

Your example only shows 6 rows of data, but how much are you likely to have in your real file?

Pete

[utahmoose]

I could live with each being in its own cell. I've managed to do what I am trying to do with values and AVERAGEIFS, so I might be able to figure it out eventually. If you know what the long formula would be, though, I would certainly appreciate it!

[utahmoose]

Will you always be looking for "Blue", or might you want to specify a different keyword? If you do then it will be easier to use a cell to enter the keyword, so which cell are you likely to use?

Your example only shows 6 rows of data, but how much are you likely to have in your real file?

Pete

No, I would actually be looking for different colors, with hundreds of rows and a handful of animals.

[Pete_UK]

Okay, here's an approach:

Use E1 to specify your keyword. Then in C1 you can have this formula:

=IF(A1=$E$1,B1,"")

then copy this down to the bottom of the data in column B by double-clicking the fill handle (the small black square in the bottom right corner of the cursor). Then put this formula in D1:

=IF(C1="","",C1)

and this in D2:

=D1&C2

and copy this down by double-clicking the fill handle. Then in E2 you can have this formula:

=INDEX(D:D,COUNTA(D:D))

which will give you all the matching values from column B in one cell, and the criteria in the cell above it - just change this to get another set.

Hope this helps.

Pete

[XOR LX]

Ok, then, assuming that the table you give above is in the range A1:B6, and that the word for which you wish to return the matches (e.g. "Blue") is in D1, enter this array formula (important that you know how to enter this type of formula in Excel) in E1 and copy to the right until you start to get blanks for the results:

=IFERROR(INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=$D1,ROW($A$1:$A$6)-MIN(ROW($A$1:$A$6))+1),COLUMNS($A:A))),"")

Regards

[utahmoose]

Ok, then, assuming that the table you give above is in the range A1:B6, and that the word for which you wish to return the matches (e.g. "Blue") is in D1, enter this array formula (important that you know how to enter this type of formula in Excel) in E1 and copy to the right until you start to get blanks for the results:

=IFERROR(INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=$D1,ROW($A$1:$A$6)-MIN(ROW($A$1:$A$6))+1),COLUMNS($A:A))),"")

Regards

Hmm, maybe I don't know how to enter array forumulas because I copy/pasted that into a workbook of exactly what I did in the post (not actual data) and it didn't work. Any suggestions?

Thanks

[utahmoose]

Okay, here's an approach:

Use E1 to specify your keyword. Then in C1 you can have this formula:

=IF(A1=$E$1,B1,"")

then copy this down to the bottom of the data in column B by double-clicking the fill handle (the small black square in the bottom right corner of the cursor). Then put this formula in D1:

=IF(C1="","",C1)

and this in D2:

=D1&C2

and copy this down by double-clicking the fill handle. Then in E2 you can have this formula:

=INDEX(D:D,COUNTA(D:D))

which will give you all the matching values from column B in one cell, and the criteria in the cell above it - just change this to get another set.

Hope this helps.

Pete

That finds the right values, but puts it as "catgoatdog". Any suggestions on how to separate them to "cat, goat, dog"? Or do I need to just do that manually you think?

Thanks!

[XOR LX]

Array formulas aren't entered like 'normal' formulas.

Go into the formula bar as if to edit the formula, then, instead of pressing just ENTER as you would normally, first hold down both CTRL and SHIFT and only then press ENTER.

If you've done it correctly you'll notice curly brackets {} around the formula.

Regards

[utahmoose]

Array formulas aren't entered like 'normal' formulas.

Go into the formula bar as if to edit the formula, then, instead of pressing just ENTER as you would normally, first hold down both CTRL and SHIFT and only then press ENTER.

If you've done it correctly you'll notice curly brackets {} around the formula.

Regards

Works like a charm. I think I can handle the rest, thanks!

[XOR LX]

You're welcome!

[utahmoose]

You're welcome!

One last question: if I have duplicates returned when I search for Blue, is there an easy way to sort it so they only show up once? For instance, if I search for "Blue" and it returns "cat, cat, goat, dog, cat", I only want "cat, goat, dog".

Thanks!

[Pete_UK]

That finds the right values, but puts it as "catgoatdog". Any suggestions on how to separate them to "cat, goat, dog"? Or do I need to just do that manually you think?

Thanks!

With my method, you can change the formula in D2 to this:

=D1&IF(OR(LEN(D1)=0,LEN(C2)=0),"",", ")&C2

then copy this down to give you the names separated by a comma and space.

Hope this helps.

Pete

[Pete_UK]

One last question: if I have duplicates ... I only want "cat, goat, dog".

If you want to avoid duplicates (and have the comma-spaces between names), you can put this formula in D2 instead:

=D1&IF(COUNTIF(C$1:C1,C2)>0,"",IF(OR(LEN(D1)=0,LEN(C2)=0),"",", ")&C2)

then copy that down.

Hope this helps.

Pete

[utahmoose]

If you want to avoid duplicates (and have the comma-spaces between names), you can put this formula in D2 instead:

=D1&IF(COUNTIF(C$1:C1,C2)>0,"",IF(OR(LEN(D1)=0,LEN(C2)=0),"",", ")&C2)

then copy that down.

Hope this helps.

Pete

Whoops, only saw that first reply. Sorry and thanks, worked great!!!!

[Pete_UK]

Okay, well thanks for marking the thread as Solved (and the other one). It's always worth re-visiting your threads, because you only get an email notification of the first reply since you last read it - there could be many other replies waiting for you that you will not be aware of.

Also, it is not necessary to quote complete posts - just edit the reply down to the bit that is relevant to your follow-up comment.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[jaysakle]

Dear Pete_UK,

Thank you for the link. I gone through the link. I found the formula is based on 'Number' and my formula is based on "Text", how to convert it for text? Will explain please for me!

[Pete_UK]

You shouldn't hijack someone else's thread - I'll respond in your own thread.

Pete