IF contains THEN return cell

[lavezzi7]

Hey guys,

I have an excel sheet where cells A1-A10 containing text.

Then i would like to get the cells in another sheet but only if they contain a certain text.

For instance, I would like to place in the new sheet a cell (among A1-A10) that contains the text "win".

The formula should return the whole text in the cell and if others have also the same text, they should be shown in another cell.

Any ideas of how can I do that?

Thanks in advance!

 

[than_gold]

You can try with Hlookup or Index+Match with wild card searching "*win*".

 

[Marcelo Branco]

Assuming data in Sheet1 A1:A10

Try this array formula in, say, Sheet2 A2 copied down
=IFERROR(INDEX(Sheet1!A$1:A$10,SMALL(IF(ISNUMBER(SEARCH("win",Sheet1!A$1:A$10)),ROW(Sheet1!A$1:A$10)-ROW(Sheet1!A$1)+1),ROWS(A$2:A2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[lavezzi7]

It did not really worked for me, maybe I am doing somthing wrong, to clarify it better:

https://ibb.co/dZtGN6

I am trying to find a formula that will look in the range of the left and return the ones which contain the word win at another cell.

 

[Marcelo Branco]

Try

Array formula in D2 copied down
=IFERROR(INDEX(A$2:A$13,SMALL(IF(ISNUMBER(SEARCH("win",A$2:A$13)),ROW(A$2:A$13)-ROW(A$2)+1),ROWS(D$2:D2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

Formula in E2 copied down
=IF(D2="","",VLOOKUP(D2,A$2:B$13,2,0))
confirmed with just Enter

M.