SOLVED

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

Copper Contributor

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

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 Replies
best response confirmed by MDLeach (Copper Contributor)
Solution

# Re: Find the matching cell and then return the cell above it.

=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.

# Re: Find the matching cell and then return the cell above it.

Thank you! Works perfectly.
1 best response

Accepted Solutions
best response confirmed by MDLeach (Copper Contributor)
Solution

# Re: Find the matching cell and then return the cell above it.

=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.