if-statementgoogle-sheetscountmaxoffset

Return LAST CELL POSITION based on TWO criteria

I'm having trouble returning the LAST CELL POSITION based on two criterias:

  1. cell = "R"
  2. wherever position #1 (above) is found, if this position +3 columns = F2

I found a formula that helped me to do this for returning last cell position for the first criteria:

=max(arrayformula(if(DATA!T5:5="R", COLUMN($3:$3), 0)))

*the above formula works.

But I'd like to add criteria #2. What I'm hoping for is something like:

=max(arrayformula(if(AND(DATA!T5:5="R", OFFSET(DATA!T5:5,,3)=$F$2), COLUMN($3:$3), 0)))

*this above formula doesn't work- returns 0 when it shouldn't.

attached is the file, with cell in question highlighted in turquois.

text

tried offset function but returns incorrect value

Solution

  • try:

    =MAX(INDEX(IF(IFERROR((DATA!T5:5="R")*(OFFSET(DATA!T5:5,,3)=$F$2)), COLUMN($3:$3), 0)))

    enter image description here

    or try without IF:

    =MAX(INDEX(IFERROR((DATA!T5:5="R")*(OFFSET(DATA!T5:5,,3)=$F$2)*COLUMN($3:$3))), 0)