Return Last Value in Range Using INDEX MATCH

[brandonej]

Hello all. I'm trying to create a lookup formula using INDEX MATCH that returns the last value in a range. It usually returns the first value that satisfies the lookup criteria (I think).

I need to find the ending balance of each week, and summarize those values in a table. (See image).

The formula I used in an attempt to return the ending balance for week 1 is: =INDEX($C$5:$W$5,MATCH(C9,$C$3:$W$3,0))

 

[Alex Blakenburg]

Since you have MS365 you can use XMatch

=INDEX($C$5:$W$5,XMATCH(C$9,$C$3:$W$3,0,-1))

Alternatively you could swap to XLookup

=XLOOKUP(C$9,$C$3:$W$3,$C$5:$W$5,"",0,-1)

The "-1" at the end is the Search Mode with -1 being Search Last-to-First.

 

[brandonej]

Since you have MS365 you can use XMatch

=INDEX($C$5:$W$5,XMATCH(C$9,$C$3:$W$3,0,-1))

Alternatively you could swap to XLookup

=XLOOKUP(C$9,$C$3:$W$3,$C$5:$W$5,"",0,-1)

The "-1" at the end is the Search Mode with -1 being Search Last-to-First.

That did it. Thank you!!

 

[Alex Blakenburg]

You’re welcome. Glad I could help.