Find the value of a cell one above

%3CLINGO-SUB%20id%3D%22lingo-sub-2226189%22%20slang%3D%22en-US%22%3EFind%20the%20value%20of%20a%20cell%20one%20above%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2226189%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20create%20a%20dashboard%20that%20gives%20a%20weekly%20update%20on%20my%20business.%20I%20have%20managed%20to%20use%20the%20LOOKUP%20function%20to%20find%20the%20last%2Fmost%20recent%20value%20in%20my%20sales%20list.%20This%20works%20well%20as%20it%20updates%20on%20the%20dashboard%20when%20I%20add%20in%20the%20new%20value%20for%20the%20week%20just%20gone.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20also%20want%20another%20cell%20to%20show%20the%20second%20most%20recent%20value%20(i.e.%20the%20one%20above%20the%20value%20found%20first)%20to%20compare%20this%20week's%20value%20to%20last%20week's.%20How%20do%20I%20do%20that%3F%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2226189%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2226226%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20the%20value%20of%20a%20cell%20one%20above%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2226226%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1004047%22%20target%3D%22_blank%22%3E%40jacktaylor110%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20sales%20data%20are%20on%20a%20sheet%20named%20sales%20in%20column%20K.%3C%2FP%3E%0A%3CP%3EThe%20last%20(bottommost)%20entry%20is%20returned%20by%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(Sales!K%3AK%2CMATCH(9.99999999999999E%2B307%2CSales!K%3AK))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20next%20to%20last%20entry%20is%20returned%20by%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(Sales!K%3AK%2CMATCH(9.99999999999999E%2B307%2CSales!K%3AK)-1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2226330%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20the%20value%20of%20a%20cell%20one%20above%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2226330%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1004047%22%20target%3D%22_blank%22%3E%40jacktaylor110%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(Sales!K%3AK%2CSEQUENCE(2%2C%2CXMATCH(%2CSales!K%3AK)-2))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eto%20return%20both%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I'm trying to create a dashboard that gives a weekly update on my business. I have managed to use the LOOKUP function to find the last/most recent value in my sales list. This works well as it updates on the dashboard when I add in the new value for the week just gone. 

 

However, I also want another cell to show the second most recent value (i.e. the one above the value found first) to compare this week's value to last week's. How do I do that? Thanks

2 Replies

@jacktaylor110 

Let's say the sales data are on a sheet named sales in column K.

The last (bottommost) entry is returned by

 

=INDEX(Sales!K:K,MATCH(9.99999999999999E+307,Sales!K:K))

 

The next to last entry is returned by

 

=INDEX(Sales!K:K,MATCH(9.99999999999999E+307,Sales!K:K)-1)

@jacktaylor110 

As variant

=INDEX(Sales!K:K,SEQUENCE(2,,XMATCH(,Sales!K:K)-2))

to return both