CODEWITHSUNDEEP
google-sheetsgs-vlookup
CGross
CGross

Reputation: 513

Most recent and next most recent values in a column

I'm using: =FILTER(B:B,ROW(B:B)=MAX(FILTER(ROW(B:B),NOT(ISBLANK(B:B)))))

Edit: To clarify. I am looking to get the most recent value from B, the 2nd most recent value from B, and the most recent value from A where B is not blank. In my example, the most recent from B is 218, the 2nd most recent is 224.4, and the most recent from A where B is not blank is 12/30/14. Ex:

blank
blank
VALUE1 <--- 2nd to max
blank
blank
...variable number of blanks
VALUE2 <-- max

Upvotes: 0

Views: 150

Answers (1)

pnuts
pnuts

Reputation: 59475

Ignoring the Title:

the most recent value from B 

=vlookup(1E+100,B:B,1)

the 2nd most recent value from B 

=index(B:B,MATCH(1E+100,INDIRECT("B1:B"&MATCH(1E+100,B:B)-1)))

the most recent value from A where B is not blank 

=index(A:A,MATCH(1E+100,INDIRECT("B1:B"&MATCH(1E+100,B:B))))

Upvotes: 1

Related Questions