Excel 2002: How to look up for the last entry ?

[Mr. Low]

Hi,

Lets consider the following illustration:

A B
29 Type Qty
30 P 200
31 Q 201
32 R 202
33 P 203
34 Q 204
35 R 205
36 P 206
37 Q 207
38 R 208
39
40 First Qty
41 P 200
42 Q 201
43 R 202
44
45 Last Qty (what I get)
46 P 200
47 Q 204
48 R 208

Correct answer should be

Last Qty
P 206
Q 207
R 208

I do not have any problem in getting the first quantity from table A29:B38
by entering =VLOOKUP(A41,A$29:B$38,2,FALSE) at cell B41.

However when I replace the third agument of the formula by "TRUE", I could
not get the last quatity at A46:B48.

May I know what is the right formula to use ?

Thanks

Low

 

[Mike H]

Hi,

use this to return the last match

=LOOKUP(2,1/($A$29:$A$38=A41),$B$29:$B$38)

Mike

 

[sheryarkhan]

for the last match try
=lookup(2,1/($a$1:$a$29=p),$b$1:$b$29)

 

[Don Guillett]

Look in the help index for MATCH and then INDEX
Last
=INDEX($B:$B,MATCH($D1,$A:$A))

First
=INDEX($B:$B,MATCH($D1,$A:$A,0))

 

[JLatham]

Since I suspect that you may have more than just 3 groups of these to deal
with, I'll steal from Mike H and sheryarkhan's solutions and change a piece
so that it will return the last group even if you have 20 or 30 or 100
instead of just 3:

=LOOKUP(COUNTIF(A$29:A$38,A46)-1,1/($A$29:$A$38=A46),$B$29:$B$38)
that's for the P match at row 46.

 

[Mr. Low]

Hi Mike,

Thanks for your formula.

It works.

Best Regards

Low