MATCH and OFFSET function

[quintans1]

Hello, I am trying to return two values in separate cells based on what I place in another cell.

For example, A1 is the title of the column, A2 contains the value "M1", A3 contains "M2", A4 contains "M3",........A99 contains "M98".

In cell C2, I want to enter a value that could be found in Column A, but in cell C1 i want to have a formula that will return the cell 11 rows above it. So if I enter the value M24, I want the formula to search column A, find "M24" and return the value "M13". Additionally if the value doesn't have 11 rows above it, then the value would return "M1" given M2 through M11 would require this result.

Any ideas?

[Cutter]

Try
=INDEX(A:A,MAX(2,MATCH(C2,A:A,0)-11))

[quintans1]

That worked, thank you

[Cutter]

You're welcome. Don't forget to mark your thread as SOLVED (click on Forum Rules @ top of page and see rule #9 for instructions if needed).