assigning value from cell below

[rogster001]

Hi all,

I am checking to find if a cell in a range contains a string,
if so i then need to output the value of the cell immediately below the one containing the string How can i do this? It will be very easy in VBA i know, but i would like to just use an expression in the formula bar

I have tried a few things and can find the cell containing the string no problem, but i do not know how to then collect the value of the cell below the one containing the target string

 

[doofusboy]

Something like this should work:

=IF(FIND("your_text_here",A1,1)>0,OFFSET(A1,1,0),"")

This searches for "your_text_here" in cell A1 and if it finds it, returns the value from cell A2, otherwise it returns a blank.

 

[Ron Coderre]

Try something like this....
A1: a value to find...e.g. frisbee

B1:B100 contains values (text, amounts, blanks, whatever...)

This regular formula returns the value of the cell directly below the
Col_B cell that contains the A1 value ("frisbee" in this case)
C1: =INDEX(B1:B100,MATCH(A1,B1:B100,0)+1)

Is that something you can work with?

 

[rogster001]

This stuff looks good guys, thanks for the help,on closer observation the first example might not work, i should explain further: I need to define a range in the formula ideally, rather than drag the formula across a range, in which case yes as soon as the cell containing the target string was found then it is easy to return 'a2'..relative to the cell currently containg the formula,
But what is required is something that expresses:
(formula assigned to a single cell)
"If a cell in the range a1:a30 contains "Revenue" then value = value of the cell immediately below the one that was found containing 'Revenue' "

The cell containing revenue is not fixed you see, it is exported data that fills more or less rows depending on volume of data. the totals are appended at the bottom of the list, on whichever row it ends on

 

[rogster001]

Try something like this....
A1: a value to find...e.g. frisbee

B1:B100 contains values (text, amounts, blanks, whatever...)

This regular formula returns the value of the cell directly below the
Col_B cell that contains the A1 value ("frisbee" in this case)
C1: =INDEX(B1:B100,MATCH(A1,B1:B100,0)+1)

Is that something you can work with?

I got it now, that is sweet as a nut, thanks, it is even better than hoped in that i get two birds with one stone there - My column heading on my results sheet can also serve as the 'value to find' As they are one and the same anyhow!