CODEWITHSUNDEEP
excelif-statementbooleanconditional-operator
Chris Tavano
Chris Tavano

Reputation: 3

Finding the last occurrence of text in a column to result in adjacent cell value

This is the current formula that works well when copied down, but now I want the false value to no longer be "", and instead be the value that is 2 columns adjacent to it. 

=IF(OR(F2="Craps",F2="$$$",F2="PtEst"),"ComeOut","")

For instance, when this formula is in cell G3, I want it to return the value in E2. However, this is not a consistent relative reference.

This needs to be conditional based upon the previous cell being true/false, and want the result to be from the most recent ComeOut to take the value in the same row as the most recent ComeOut and return the value in column E that corresponds with that row of the most recent ComeOut.

Any help or insight is greatly appreciated.

Here's my spreadsheet:

My spreadsheet

Upvotes: 0

Views: 652

Answers (3)

Kyle
Kyle

Reputation: 564

This should work in a single column. You must enter it as an array formula by hitting Ctrl+Shift+Enter to confirm. In G2:

=IF(OR(F2="Craps",F2="$$$",F2="PtEst"),"ComeOut",OFFSET(F2,-MIN(IF(G$1:G1="Comeout",ROW(G2)-ROW(G$1:G1),"")),-1))

Upvotes: 0

BrakNicku
BrakNicku

Reputation: 5991

To get expected output please try:

  • leave formula in G2 as it is

  • in G3 enter:

    =IF(OR(F3="Craps",F3="$$$",F3="PtEst"),"ComeOut",IF(G2="ComeOut",E2,G2))

and fill it down.

If the the previous value in G column is "ComeOut", then take corresponding E value. If not - copy previous G column value.

Upvotes: 1

Nate May
Nate May

Reputation: 4062

I figured out a way that requires 2 more columns, but you can hide them.

Leave column G as is

Cell H2: =IF(G2="ComeOut",E2,H1) --- Drag this formula down

Cell I2: =IF(G2="ComeOut",G2,H2) --- Drag this formula down

Your desired result will be in column I, but you can rearrange as necessary

Upvotes: 0

Related Questions