VLOOKUP if target appears more than once in a list

[Kartz]

Hi
I am trying to use a Vlookup formula from a list of items

THIS IS PAGE 1 (with the list)
A B
1 Sales Qty
2 Apple 3
3 Mango 4
4 Orange 1
5 Apple 2

I want to show in PAGE 2 as follows:
A B
1 Apple 3
2 Apple 2

When I try to vlookup the values for Apple in Page 2 using
=vlookup(A1,page1$A$1:$B$5,2,0), I get "3". But even if I drag the formula
down, I still get 3 as the formula picks up the first value relating to
apple.

Can someone tell me how to use a formula to pick up the "2" in the second
line? Should I be using some other formula other than VLOOKUP?

 

[T. Valko]

On Sheet2:

A1 = Apple
A2 = Apple

This array formula** will return the corresponding values in *descending
order*. Based on your sample the results would be:

...........A..........B
1.....apple.......3
2.....apple.......2

Array entered** in B1:

=LARGE(IF(Sheet1!A$2:A$5=A1,Sheet1!B$2:B$5),COUNTIF(A$1:A1,A1))

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

If you want the results to appear in the *exact* same sequence as they
appear on Sheet1 then the formula gets a lot more complicated.

 

[Niek Otten]

<a lot more complicated>

But if you do need it, here is a description of how to do that:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx