Return Multiple Rows if Value Exists in Cell

[sheepdemon]

So, I'm using a SEARCH function to check for text in a cell.

=(IF(ISNUMBER(SEARCH("<value>",'Sheet1'!B15)),TRUE, FALSE))

This works fine, and I get TRUE if the <value> exists and FALSE if if does not, as you'd expect.

However what I'm trying to do is have the "True" value be "Several lines of text". That doesn't work as the max formula length is 256 characters, so I tried making a new sheet with 9 rows of data in which I am attempting to pass using :

=(IF(ISNUMBER(SEARCH("<value>",'Sheet1'!B15)),Sheet3:A1:A9, FALSE))

This still returns False if the value does not exist, but gives a #Value! error if the statement is true.

What changes should I make to this code in order to have this work? Initial Googling suggests possible an array is required but I'm not 100% sure I'm looking at good advice.

 

[sheepdemon]

=(IF(ISNUMBER(SEARCH("<value>",'Sheet1'!B15)),Sheet3!A1:A9, FALSE))

Typo in my second formula corrected.

 

[Jimmypop]

Hi there

Maybe try

=(IF(ISNUMBER(SEARCH("<value>",'Sheet1'!B15)),Sheet3!A1:A9, FALSE))

 

[Peter_SSs]

Just a point of information. You do not need the IF(....,TRUE,FALSE) in that first formula
=(IF(ISNUMBER(SEARCH("<value>",'Sheet1'!B15)),TRUE, FALSE))
This would do exactly the same thing
=ISNUMBER(SEARCH("<value>",Sheet1!B15))

What is the name of the sheet that this formula is on?

Are you trying to return the nine rows of data into 9 cells, or
Are you trying to combine those 9 rows of data into a single cell by the formula?

What exactly do you want the formula to return if the value is not found in B15?

 

[sheepdemon]

Hi,

Think I might have fixed this my end.

Basically what I was trying to do was return the 9 rows into the one cell, I'm using this to try and auto-generate SQL code from values provided from a different sheet. I think its one of those things where I'm over-engineering something but I don't really have the correct tools for the job, so Excel is being leveraged to do the work of like 3 different programs. Joy.

Instead of trying to return 9 Rows of SQL code I've just condensed the code into 1 cell with semi-colons seperating the lines of SQL code. It looks like an absolute tyre fire now, but that is probably in line with the methodology I'm using to generate this code, and more importantly it seems to work, so probably fine.

Thanks,

 

[sheepdemon]

The script is looking at a cell like this :

Running the "IsNumber(Search" for each of the countries, and then saying "If AT exists, show the code to register the document in AT, if not, generate a thing that says "--Not registered in AT""

Then I CTRL A, copy all the text for the relevant countries, and smash this into SQL. I regret everything about this process.

 

[Peter_SSs]

Basically what I was trying to do was return the 9 rows into the one cell,

Something like this maybe?

=(IF(ISNUMBER(SEARCH("<value>",Sheet1!B15)),TEXTJOIN(" - ",1,Sheet3!A1:A9),""))