Formula to copy and paste if referenced cell has extended value (more than one word)

[stpeter]

Hi All,
This is related to my last post for a formula to copy and paste if referenced cell has specific value, and because that was successfully answered I closed it.
Here is my original thread:
I have a long list of data with specific values listed in Column A. I need a formula to be used on a separate tab, that will go down the list of the referenced tab, and referenced column/cell, and return the whole row if a specific text is in that referenced column/cell. If there the referenced cell is blank then it should return a blank, and not show the formula or “0”, “value”, etc. Unfortunately the formula I’m using (below) is returning the data in cell A2 (Row 1 is the column headers).
=IFERROR(INDEX('Produce'!A$2:A$250,SMALL(IF('Produce'!$A$2:$A$250="Apples",ROW('Produce'!$A$2:$A$250)-1),ROWS($A$1:A1))),"")

One solution was =IFERROR(INDEX(Produce!A$1:A$250,SMALL(IF(Produce!$A$1:$A$250="Apples",ROW(Produce!A$1:A$250)),ROW(A1))),"")
Control+Shift+Enter.

However, I do need an extension to that formula. I have two or more word products – “Strawberries – Organic”, “Seedless Grapes (Purple)”, “Seedless Grapes (Green), etc., and the formula returns only the first word, and the remaining columns i.e. B2:F2 are blank.

Any suggestions?

SP

[CAntosh]

Are you able to post a small sample workbook (Use: Go Advanced > Manage Attachments) so we can get a better sense of what your issue looks like?

[stpeter]

Mais oui bien sûr

and thank you!

[CAntosh]

I'm not sure I'm fully understanding what you're after. The formula from your initial solution can be adjusted to accommodate new (and multi-word) produce types on separate sheets (see attached). e.g.:

=IFERROR(INDEX(Produce!A$1:A$250,SMALL(IF(Produce!$A$1:$A$250="Seedless Grapes",ROW(Produce!A$1:A$250)),ROW(A1))),"")

Is the issue that you're looking to combine types? Should “Seedless Grapes (Purple)” and “Seedless Grapes (Green)" be on the same sheet?

[JohnTopley]

Not clear (to me) exactly what you want ..

=IFERROR(INDEX(Produce!A$1:A$250,SMALL(IF(ISNUMBER(SEARCH("Strawberries",Produce!$A$1:$A$250)),ROW(Produce!A$1:A$250)-ROW(Produce!$A$1)+1,""),ROWS($A$1:A1))),"")

Enter with Ctrl+Shift+Enter

copy across and down

will return any text containing "Strawberries".

[José Augusto]

Hi
Try this formula as a helper column in Sheet 'Apples' (Column I where I1 is Apples) and copy down

Please Login or Register  to view this content.

In same Sheet use in A2 and copy down and right

Please Login or Register  to view this content.

Note: You do not need Produce!H1:O29
Do the same for the other products
See the file

[stpeter]

Hi All,
My apologies for the unclear thread.
Thank you to all for responding so quickly.

What I needed was a return of the whole row if a particular word was in the formula. I did not want to copy the entire set of words as some product names are rather long, as well as I can keep some similar products in the same worksheet.

I have used all three formulae – albeit in different workbooks, and watching the magic work is so satisfying. These have saved me a whole lot of extra work! Merci!

I will close the thread for now.

[CAntosh]

For what it's worth, you can modify the strategy proposed by JohnTopley and me above with the following formula:

=IFERROR(INDEX(Produce!C$1:C$250,SMALL(IF(ISNUMBER(SEARCH("Strawberries",Produce!$A$1:$A$250)),ROW(Produce!C$1:C$250)),ROW(C7))),"")

To return all matches that CONTAIN "strawberries", instead of just exact matches (e.g. it would include "strawberries organic" from your sample)

[stpeter]

This is exactly what I wanted! Thank you Cantosh.
I have a new need related to this and regarding dates. However, in keeping with Forum rules, I will create a new post. Please stay tuned. Merci.