lookup value in range

[richfm1]

i need help with a formula in col B that looks at the previous 9 rows in col A for a specific value and if any of those cells in col A have that value then answer = true.... the formula in col B would repeat for rows 1-100... how can this be done

example:
cell b30 contains formula: if any cells A22 thru A30 have the value "blue" then b30 = "true" else "false"
b31: if any cells A23 thr A32 have the value "blue" then b31 = "true" else "false"
b32: if any cells A24 thr A33 have the value "blue" then b32 = "true" else "false"
... and so on

thanks for the help

 

[T. Valko]

i need help with a formula in col B that looks at the previous 9 rows in col A for a specific value and if any of those cells in col A have that value then answer = true.... the formula in col B would repeat for rows 1-100... how can this be done

example:
cell b30 contains formula: if any cells A22 thru A30 have the value "blue" then b30 = "true" else "false"
b31: if any cells A23 thr A32 have the value "blue" then b31 = "true" else "false"
b32: if any cells A24 thr A33 have the value "blue" then b32 = "true" else "false"
... and so on

thanks for the help

Are those range offsets correct?

If:
B30 looks in A22:A30
Shouldn't B31 look in A23:A31
Shouldn't B32 look in A24:A32

?????

 

[Aladin Akyurek]

i need help with a formula in col B that looks at the previous 9 rows in col A for a specific value and if any of those cells in col A have that value then answer = true.... the formula in col B would repeat for rows 1-100... how can this be done

example:
cell b30 contains formula: if any cells A22 thru A30 have the value "blue" then b30 = "true" else "false"
b31: if any cells A23 thr A32 have the value "blue" then b31 = "true" else "false"
b32: if any cells A24 thr A33 have the value "blue" then b32 = "true" else "false"
... and so on

thanks for the help

Try...

B22, copy down:

=IF(ROWS($A$22:A22)>=9,ISNUMBER(MATCH("blue",OFFSET(A22,0,0,-9),0)),"")

 

[richfm1]

thanks for the reply but it is not working properly. the formula I am using is:
=IF(C13="","",IF(ISTEXT(G13),"",IF(OR(F13="sub",F12="sub",F11="sub",F10="sub",F9="sub",F8="sub",F7="sub",F6="sub",F5="sub",F4="sub",F3="sub"),"",G13)))

it works but there should be a simpler way... the formula is used for cells c10:c3000

thanks for the help

 

[njimack]

Your entire formula could be condensed to:
=IF(OR(C13="",ISTEXT(G13),COUNTIF(F3:F13,"sub")),"",G13)

 

[Aladin Akyurek]

thanks for the reply but it is not working properly. the formula I am using is:
=IF(C13="","",IF(ISTEXT(G13),"",IF(OR(F13="sub",F12="sub",F11="sub",F10="sub",F9="sub",F8="sub",F7="sub",F6="sub",F5="sub",F4="sub",F3="sub"),"",G13)))

it works but there should be a simpler way... the formula is used for cells c10:c3000

thanks for the help

Not only "blue" is changed to "sub", also the ranges are different. Would you post a sample where you could reduce the window size from 9 to 3 and lists the desired results?