Formula that returns the value of a cell underneath a searched text.

[IceSnakeR]

Hello,

I need help with the example underneath.

I would like that a formula that will search an interval ( in my example: A1:C5) for a key word( B2 in my example that contains the text "COST MAX") and return the value of the cell bellow( B3 in my case with the text "VALUE 1"). I would like to mention that in my situation the position of the text may change, I could have the text“COST MAX” present in another cell ( C1 ) and the formula would have to return the value of the cell undearneath ( C2).

	A	B	C
1	Information
2		COST MAX	TOTAL
3	SUB	VALUE 1	VALUE 2
4
5	N/A	MAX	LOW

<tbody>
</tbody>

P.S. This request I consider it to be more difficult, but if someone can help I’ll be grateful. My additional question is to search the interval with only a part of a text, in my example “COST MAX” is the hole word, I would like to have the possibility to do it with “COST” or “MAX”.

Thank you,

Have a nice day,
Chris

 

[Special-K99]

What do you want the result to be if "COST MAX" occurs in BOTH columns B and C ?
Are there only two columns (B and C) you want to search or will there be more?
What do you want to be returned if COST MAX is the last value in the column (since there wont be another value below COST MAX) ?
Is it possible that COST MAX may occur more than once in the same column?

 

[mikerickson]

This CSE formula should do it
=INDEX(A2:C6,MAX((A1:C5="Cost Max")*(ROW(A1:C5)+COLUMN(A1:C5)/10)), 10*MOD(MAX((A1:C5="Cost Max")*(ROW(A1:C5)+COLUMN(A1:C5)/10)),1))

This needs to be entered with Ctrl-Shift-Enter (CMD+RETURN for Mac)

 

[IceSnakeR]

Hy Special-K99

1. A certain name,string occurs only once
2. The interval will be bigger
3. There will always be an value underneath "Cost Max"
4. No, it will occur only once

The solution that mikerickson has provided works for me. Thank you

 

[IceSnakeR]

Thank you mikerickson ,

your solution works for me. Thank again and have a nice day.