changed sheet names, INDIRECT() not working in any combination

I changed my sheet names and now functions aren't working.

INDIRECT no longer seems to work in any combination with index, cell("contents", find, search, match, or vlookup. For example, this doesn't work =MATCH("search results",INDIRECT(CELL("CONTENTS",F2),0))

The only way that I can see what is in a cell on another sheet is to do =sheetname!cell. I can't find what is in a cell a certain number of cells below it, I can't search within search results. I was doing this as a match concatenate match, and sometimes match indirect concatenate match. I have also used index indirect or indirect index somehow successfully before.

Additionally, match no longer works even when entering the range directly into the function like this: =MATCH("results",Google1!A1:Google1!A300,0). That is because the cell being found actually says "search results". Now with match it only finds the cell if I search for the entire cell contents like this =MATCH("search results",Google1!A1:Google1!A300,0). That doesn't work because I don't always know what else is in the cell.


The function I need to work at the moment is below. I'm trying to find the cell that says "search results", then look from that row down (same column) for the first cell that says "1. " But even if this function can be made to work, I really need an overall solution for why indirect isn't working because this is going to be a major problem.

=SEARCH("1. ",CONCATENATE("Google1!A",MATCH("search results",Google1!A1:Google1!A300,0),":Google1!A300"))

Please help!

Google1!A39:Google1!A300

The above is the result of functions in cell F2. Why won't this work to search those cells? =MATCH("search results",INDIRECT(F2),0)

I am getting a lot of #REF! errors with indirect. The spelling and spacing are correct.