Finding Last value

[roanscar]

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]

 

[Andrew Poulsom]

If your data is sorted as in your example:

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

 

[roanscar]

Thank you sir!

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

 

[Andrew Poulsom]

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]

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