Finding Last value

roanscar

New Member
Joined
Oct 19, 2006
Messages
3
I know I have seen this on the boards before but now can't find it.

Here is a sample of what the data will look like for columns a & b:

A B
Red 25
Red 30
Red 14
Blue 25
Blue 13


I am trying to get a formula so that it will return the last value entered in column B based on the value in column A[/i]
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If your data is sorted as in your example:

=INDEX(B:B,MATCH("Red",A:A,FALSE)+COUNTIF(A:A,"Red")-1)
 

roanscar

New Member
Joined
Oct 19, 2006
Messages
3
Thank you sir!

Out of curiousity, what would the formula be if the reds and blues were intermingled?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try eg:

=INDEX(B1:B5,MAX(IF(A1:A5="Red",ROW(INDIRECT("1:"&ROW(A5))),0)))

which must be confirmed with Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surround this array formula with curly braces {}.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
=INDEX(B1:B5,MAX(IF(A1:A5="Red",ROW(A1:A5)-ROW(A1)+1)))

which needs to be confirmed with control+shift+enter, not just with enter.

A different idiom is:

=LOOKUP(2,1/(A1:A5="Red"),B1:B5)

which needs just enter.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,861
Messages
5,544,723
Members
410,630
Latest member
JFORTH97
Top