Look in Several Cell of a Row

[dparteka]

This formula looks in A1 for the text "SPE |" and if found it displays the entire row, if not found it displays nothing... it works perfectly good. I'm trying to figure out how to get it to look in several or all cells of the row and again if it finds the text in any of those cells it’ll display the entire row. Any help will be greatly appreciated… thank you.

=IF(OR(ISNUMBER(FIND({"SPE |"},'[Bill of Material.xlsx]Table 1'!A1,1))),'[Bill of Material.xlsx]Table 1'!A1,"")

 

[NARAYANK991]

Hi ,

Either of the following array formulas , to be entered using CTRL SHIFT ENTER , should do it :

=IF(OR(ISNUMBER(FIND({"SPE |"},Sheet1!A1:G1,1))),INDEX(A1:G1,MATCH(TRUE,ISNUMBER(FIND({"SPE |"},Sheet1!A1:G1,1)),0)),"")

or

=IFERROR(INDEX(A1:G1,MATCH(TRUE,ISNUMBER(FIND({"SPE |"},Sheet1!A1:G1,1)),0)),"")

Narayan

 

[dparteka]

Nararyan... thank you for helping me out with this. I am having trouble getting your formulas to work and I'm sure it's because my description of the issue could have been better described.

I have attached two spreadsheets; the "Result" is looking in the "Source". You'll see that A1 in "Result" displays the text and A2 does not; this is because "SPE |" is showing up in column-D of "Result".

The goal is… if "SPE |" appears in any column of "Source" then that entire row must be mirrored and merged into column-A of "Result".

 

[NARAYANK991]

Hi ,

I did not understand that concatenation is involved , since this is a feature in which Excel is lacking , unless you use Excel 2013 , in which case you can use a function such as TEXTJOIN.

See the attached file , where I have used helper columns.

Narayan

 

[dparteka]

It took me a while to wrap my head around your code, "helper columns", pretty brilliant, thanks for the TEXTJOIN tip... very helpful in all respects.