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?

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),COUNTI F(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.

--
Biff
Microsoft Excel MVP


"Kartz" wrote in message
...
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?

<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/ex...260381033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"T. Valko" wrote in message
...
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),COUNTI F(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.

--
Biff
Microsoft Excel MVP


"Kartz" wrote in message
...
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?