Find the matching cell and then return the cell above it.

[MDLeach]

I have a "cover page" where you select what fiscal year it is. Call this "cover page"

I have a column with all the fiscal years, historical, current, and future. Call this "fiscal year column"

E.g.

FY21

FY22

FY23

FY24

I want to be able to write a formula that says, find the fiscal year selected on the cover page in the fiscal year column and then return the cell above it. I.e. Find FY24 in the fiscal year column and return FY23.

I also want to be able to write a formula that says, find the fiscal year selected on the cover page in the fiscal year column and then reutnr the cell two cells above it. I.e. Find FY24 in the fiscal year column and return FY22.

[#2 · Accepted solution]

@MDLeach 

=INDEX(A:A,MATCH(D1,A:A,0)-2)

This formula finds the FY two cells above the matching FY.

=INDEX(A:A,MATCH(D1,A:A,0)-1)

This formula finds the FY one cell above the matching FY.

I assume that the FY are sorted in ascending order in column A.

[#3]

Thank you! Works perfectly.