Search and Return the value of a cell that begins with specific text?

Need a formula to return the value of a cell that begins with specific text "Report Parameters".

I paste raw data to a sheet and "Column A" always has one row near the bottom that starts with "Report Parameters". I want the text of that cell to show in another palce in my template. The problem is that depending the report, that text gets pasted to different rows, but always in Column A. Lets say, I want the value of that cell to show up in cell B1

Hi, welcome to the forum

Try this...
=INDEX(A:A,match("Report Parameters*",A:A,0)

Is there perhaps a space at trhe start of that text?

Also, try just this...
match("Report Parameters*",A:A,0)
and reduce (1 at a time) the letters in "Report Parameters*"

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

The problem is that A21 is more than 255 characters in length and MATCH will not work strings > 255.


Wrong again!!!!

I didn't download your file.

Try something like this:

Array entered**:

=INDEX(A2:A10,MATCH(TRUE,LEFT(A2:A10,17)="Report Parameters",0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Alternative option:

=LOOKUP(2,1/(SEARCH("Report Parameters",A:A)=1),A:A)

returns last cell found in column A starting with "Report Parameters"