Vlookup with Duplicate Results

G

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
How do I get the Vlookup to not return duplicate results?

For Example:

Column A Column B
x 2
y 1
Z 3
A 1

Now if I am ranking them, and there are two 1's, and that is the best rank in the group, how do I get the Vlookup to return in the first lookup Y, then in the next lookup one cell down, the vlookup result would A. Right now my formula has it where it consistently pulls Y for both formula's.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
 
Upvote 0
The duplicates are in column B. So if I look up Column B for the Value of 1, the formula always returns the value Y. What I want it to do is, for the first formula the value of Y is fine, but then in the cell below it, I would like the same formula to return the value A.

My original post, I have the columns mixed up, sorry.
 
Upvote 0
gottimd said:
The duplicates are in column B. So if I look up Column B for the Value of 1, the formula always returns the value Y. What I want it to do is, for the first formula the value of Y is fine, but then in the cell below it, I would like the same formula to return the value A.

My original post, I have the columns mixed up, sorry.
Click to expand...

Are you trying to obtain a Top list with the lowest value?
 
Upvote 0
Exactly, but some of the lowest values will be duplicated, in this instance the number 1. So I would want the number 1 to result in Person Y name for the #1 spot, and for the #2 spot, Person A.

There will be multiple categories, so each category will be different (Highest and Lowest). But I just need an example of one and I can handle the rest.
 
Upvote 0
gottimd said:
Exactly, but some of the lowest values will be duplicated, in this instance the number 1. So I would want the number 1 to result in Person Y name for the #1 spot, and for the #2 spot, Person A.

There will be multiple categories, so each category will be different (Highest and Lowest). But I just need an example of one and I can handle the rest.
Click to expand...

Top 1 list, based on the lowest value...
aaTop1 (Min) gottimd.xls
ABCD
1Min1
21
31
4NameScoreRankTopList
5x23y
6y11A
7Z34 
8A12 
Data


Formulas...

C5, copied down:

=RANK(B5,$B$5:$B$8,1)+COUNTIF($B$5:B5,B5)-1

D1:

=MIN(B5:B8)

which is not really part of the formula system that creates the Top 1 list.

D2: 1

1 is the N value of Top N. The value can be set as desired.

D3:

=MAX(IF(INDEX(B5:B8,MATCH(D2,C5:C8,0))=B5:B8,C5:C8))-D2

which must be confirmed with control+shift+enter instead of just with enter.

This calculates the number of ties the Nth value might have.

D5, copied down:

=IF(ROW()-ROW(D$5)+1<=$D$2+$D$3,INDEX($A$5:$A$8,MATCH(ROW()-ROW($D$5)+1,$C$5:$C$8,0)),"")

For a Top 1 list, based on the max value remove the last argument from the rank formula...

=RANK(B5,$B$5:$B$8)+COUNTIF($B$5:B5,B5)-1

If you're on Excel 2003, convert the area A2:D8 into a list via Data|List and change the rank formula for the lowest value to:

=RANK(B5,$B$5:$B$8,1)+COUNTIF($B$5:OFFSET(B5,0,0),B5)-1

and for the highest value to:

=RANK(B5,$B$5:$B$8)+COUNTIF($B$5:OFFSET(B5,0,0),B5)-1
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Lookup with 2 criterias
2
Replies
14
Views
220
Peter_SSs
Peter_SSs
excelbytes
  • Solved
Lookup With No Duplicates
Replies
2
Views
135
Peter_SSs
Peter_SSs
I
  • Question
Find the second highest attribute based on criteria. I know its a combo of index(match(large, but I just can't get it to work correctly.
Replies
1
Views
83
StephenCrump
S
J
  • Question
Repeat VBA for each column
Replies
3
Views
223
Alex Blakenburg
A
R
  • Solved
Produce "Y" if VLOOKUP value is found, produce "N" if it is not.
Replies
5
Views
42
etaf
etaf

Forum statistics

Threads
1,214,911
Messages
6,122,195
Members
449,072
Latest member
DW Draft

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top