Lookup to reference part of a string of text.

I have this formula =LOOKUP(A1,1/(B1:B10=42)/(A1:A10=A1),C1:C10) which does most of what I want it to. However I want it so C1:C10 will pull the last cell in the range containing the word "Week" as part of a string of text. Can anyone tell me how I can do this?

It would be helpful to have some context and/or a sample file. We dont know what the formula currently does and have no context to determine what changes need to happen for it to do what you want.

What I can say so far is that using LOOKUP with the 3rd argument (result_vector) basically makes it act like a VLOOKUP. It finds your lookup value in the lookup_vector and returns a result in the result_vector at the same relative position. As soon as you dictate any other condition for what/how to pull a value you are outside the scope of what the individual formula was intended to do.

In context it is likely you will get a better answer

It is referencing 3 columns which can have multiple of the same values in. This will find a reference in column A, then a Value in column B and currently pull the last Comment in column C where the criteria from A and B have been matched. I want to specify the last value in C that contains the word "Week" as part of a string of text (eg. "Week 1 of 2" but this will be vary from cell to cell)