Help with IF and absolute/relative cell references

Copper Contributor

Hello, I'm using PC/Windows 10/365 Pro Plus.

 

I'm trying to create a formula in a column to check two conditions and if true return the first figure in a column ($D$8).  But for each cell below that return the next cell down in the reference column (Column D). I can only figure out how to return the value in the same row rather than return 144,350 in cell B16 followed by 160,340, 176,841 etc. in the next cells down in column B.  Is there a way to do this? 

 

 =IF($C$4="YES",IF([@Item]>=$C$3+3,C8,))

kimbera68_1-1653421226989.png

 

3 Replies

@kimbera68 

 

=IF(AND($B$4="Yes",[@Item]>=$B$3+3),INDEX($D$7:$D$26,COUNTIF(Table1[[#Headers],[Item]]:[@Item],">="&$B$3+3)),0)

Thank you so much. Works perfectly.

Hi @kimbera68 . Alternatively:

=IF(AND(B$4="yes", [@Item]>=B$3+3),
    OFFSET(D$8,COUNT(B$7:B7),0),
    "-"
)