# Finding Last value

#### roanscar

##### New Member
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]

### Excel Facts

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

#### Andrew Poulsom

##### MrExcel MVP

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

#### roanscar

##### New Member
Thank you sir!

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

#### Andrew Poulsom

##### MrExcel MVP
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 {}.

##### MrExcel MVP
=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.

Replies
22
Views
122
Replies
6
Views
81
Replies
1
Views
19
Replies
4
Views
65
Replies
9
Views
116