Using SORT to reverse the sequence of cells to do a bottom-up VLOOKUP, to some extent it finds the correct values as you can see in the image above.
The default formula I use for each row is this (Image → Column H):
=IF(E3="","",VLOOKUP("",ARRAYFORMULA(SUBSTITUTE(QUERY(SORT({SEQUENCE(COUNTIF(INDIRECT("A2:A"&row()),"<>''")),{INDIRECT("B2:B"&row()),INDIRECT("A2:A"&row())}},1,FALSE),"Select Col2, Col3"),"","§")),2,FALSE))
But for some reason I couldn't understand, this search stops working and the result is always empty.
And when trying to use the same formula with ARRAYFORMULA so you don't have to put a separate formula for each row, it always delivers the first value, even if I add the full range in each ROW (Image → Column I):
=ARRAYFORMULA(IF(E3:E="","",VLOOKUP("",ARRAYFORMULA(SUBSTITUTE(QUERY(SORT({SEQUENCE(COUNTIF(INDIRECT("A2:A"&row(E3:E)),"<>''")),{INDIRECT("B2:B"&row(E3:E)),INDIRECT("A2:A"&row(E3:E))}},1,FALSE),"Select Col2, Col3"),"","")),2,FALSE)))
What is the correct method to arrive at the expected result that is in Column F (painted in green)?
Note: The most reliable use would be using ARRAYFORMULA because I don't know how to say the maximum number of lines it can reach, so it would be more advantageous to have an option that can adjust itself according to new lines appearing automatically.
CodePudding user response：
use in row 2:
=INDEX(IF(NOT(REGEXMATCH(TO_TEXT(A2:A), "\d :\d ")),,VLOOKUP(ROW(A2:A),
IF(NOT(REGEXMATCH(TO_TEXT(A2:A), "\d :\d ")), {ROW(A2:A), A2:A}), 2, 1)))
CodePudding user response：
Add the INDEX() function to the formula
For starters, you may use the following modified formula as the basis of your formula:
=IF(INDIRECT("E"&ROW()) = "","",INDEX(SORT({SEQUENCE(COUNTIF(INDIRECT("A2:A"&row()),"<>''")),{INDIRECT("B2:B"&row()),INDIRECT("A2:A"&row())}},3,FALSE),1,3))
You may "drag down" the formula to the whole column.
Output
The expected output of this function can be seen in column G in the image below.
