Vlookup query

[Narend]

respected genious,
Can you pls guide to find the 2nd largest value or sales from a list of salesman and sales figure with the help of vlookup formula? I am able to find the 1st maximum seller but can not find the 2nd or 3rd seller.

Truly yours.

Narendra

 

[Khalid NGO]

Hi Narendra,

You can lookup for the value something like
=LARGE(A1:A10,2)
Where 2 is the 2nd largest value.

Better answer can be provided if you can share a sample of your file.

Regards,

 

[Narend]

Hi Narendra,

You can lookup for the value something like
=LARGE(A1:A10,2)
Where 2 is the 2nd largest value.

Better answer can be provided if you can share a sample of your file.

Regards,

Thanks for the quick response.
But I want the solution for the below mentioned query,

Sales Person
No. Customers
Net Sales
Profit / Loss
Joseph
8
1,592
563
John
8
1,088
397
Josh
8
1,680
753
Jamie
9
2,133
923
Jackie
10
1,610
579
Johnson
10
1,540
570
Jonathan
7
1,316
428
Jagjit
7
1,799
709
Jairam
8
1,624
621
Jessy
6
726
236
Javed
9
2,277
966
Jimmy
6
714
221
Juno
9
2,682
1,023




2. Who made more sales - Jamie or Jackie?
3. who sold 2nd highest?

(sorry unable to upload a file, system problem)

Thanks and regards

Narendra

 

[Khalid NGO]

Hi,

As Vlookup can't go back (Normally), except some arrary formulas.
You can use INDEX/MATCH which is more flexible:
=INDEX($A$2:$A$100,MATCH(LARGE($C$2:$C$100,2),$C$2:$C$100,0),1)

Regards,

 

[Khalid NGO]

You can use ROW() function instead of 2 in LARGE($C$2:$C$100,2)
if you want increment of large sales e.g. 2nd, 3rd, 4th.... (copy down)

 

[Narend]

Sales Person
No. Customers
Net Sales
Profit / Loss
Joseph
8
1,592
563
John
8
1,088
397
Josh
8
1,680
753
Jamie
9
2,133
923
Jackie
10
1,610
579
Johnson
10
1,540
570
Jonathan
7
1,316
428
Jagjit
7
1,799
709
Jairam
8
1,624
621
Jessy
6
726
236
Javed
9
2,277
966
Jimmy
6
714
221
Juno
9
2,682
1,023

You can use ROW() function instead of 2 in LARGE($C$2:$C$100,2)
if you want increment of large sales e.g. 2nd, 3rd, 4th.... (copy down)

Thanks a lot for replying so quickly and answering the same but I need the answer with the help of vlookup and index/match.

Thanku very much again

Narendra

 

[Deepak]

Hi Narend,

Doesn't think that simply vlookup or index/match produces the output what you are looking for!!

 

[Khalid NGO]

For VLOOKUP / CHOOSE
You can try:
=VLOOKUP(LARGE($C$2:$C$100,2),CHOOSE({1,2},$C$2:$C$100,$A$2:$A$100),2,0)

See how it works:
http://chandoo.org/wp/2012/09/06/formula-forensics-no-028/

 

[Narend]

For VLOOKUP / CHOOSE
You can try:
=VLOOKUP(LARGE($C$2:$C$100,2),CHOOSE({1,2},$C$2:$C$100,$A$2:$A$100),2,0)

See how it works:
http://chandoo.org/wp/2012/09/06/formula-forensics-no-028/

Thanks a lot Deepak.

with warm regards

Narendra

 

[Khalid NGO]

Thanks for the feedback Narendra.
Take Care.