Find text in column and return adjacent cell

Hi,

I have in column F a cell (which is dynamic, between F8:F400) which has the text "01 Incident Number".

I need a formula which finds this text, gets the relevant text from the adjacent cell and then pastes that adjacent text in cell C4.

Any help please???

Thanks in advance.

Assuming adjacent cell is in Column G then in c$:

=INDEX($G$8:$G$400,MATCH("01 incident number",$F$8:$F$400,0))

Change G if required

Obviously C$. should be C4!

Originally Posted by JohnTopley

Assuming adjacent cell is in Column G then in c$:

=INDEX($G$8:$G$400,MATCH("01 incident number",$F$8:$F$400,0))

Change G if required

Thanks for the very quick reply!

Unfortunately I'm getting a 0 as my result.

In the report I'm generating the "01 Incident Number" is in cell F161, with the text I'm after in cell G161. I need G161 to display in cell C4.

Am I missing something?

Thanks again.

Check G161 : you will get 0 if it is empty. I have just used the formula with data in F161 & G161 .. no problem

My spreadsheet has "20150626N1" in cell G161. This is what I want to display in cell C4, but all I get is a 0!!!!!

Thank you anyway, I'm at a loss as to what is going on.....

File attached. The information I pasted had lots of formatting issues, me thinks the problem lies there.....

There are TWO cells with "01 Incident Number" and the first has G cell blank: hence zero: MATCH will find the first occurrence of any match.

So where do we go from here?

OH NOOOOOOOO!!!!

I didn't even see that first one haha. What a goose. That throws a bit of a spanner in the works.

The idea behind all this is to clean up a horribly formatted report through a macro, including the incident number in bold at the top of the document. If the report has more than one instance of 01 Incident Number, then I give up!

Thank you so much for your time anyway.

No problem. If you feel the forum can help please come back.

That would help, but how does one go about that? What changes in the above formula?

Thanks in advance.

Hi Tony,

Thankyou, that seemed to do the trick!