• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Returning subsequent entries from VLOOKUP where duplicates exist

S

Skrocki

New Member
I have a table with similar data to this...


50 Joe

50 Cindy

50 Janet

30 Jamaal

36 Anthony

24 Stephen


I want to return the 5 highest numbers in column A of the source table into column B of the results table. Then, return the corresponding name from column B of the source table into column A of the results table.


I'm using this formula for Column A of my Results Table:

=VLOOKUP(LARGE($A$3:$A$8,1),$A$3:$B$8,2,FALSE)

=VLOOKUP(LARGE($A$3:$A$8,2),$A$3:$B$8,2,FALSE)

=VLOOKUP(LARGE($A$3:$A$8,3),$A$3:$B$8,2,FALSE)

=VLOOKUP(LARGE($A$3:$A$8,4),$A$3:$B$8,2,FALSE)

=VLOOKUP(LARGE($A$3:$A$8,5),$A$3:$B$8,2,FALSE)


And this formula for Column B of my Results Table:

=LARGE($A$3:$A$8,1)

=LARGE($A$3:$A$8,2)

=LARGE($A$3:$A$8,3)

=LARGE($A$3:$A$8,4)

=LARGE($A$3:$A$8,5)


Using the formulas above, my Results Table looks like this:

Joe 50

Joe 50

Joe 50

Anthony 36

Jamaal 30


But I wish it looked like this:

Joe 50

Cindy 50

Janet 50

Anthony 36

Jamaal 30


I've researched this like crazy and I find a lot of INDEX and MATCH formulas but none that seems to do what I want. I want the formula to work dynamically as the data changes, so sometimes there will be no duplicates and other times 2 or more (in this case 3).


Did I present that clearly enough? Thanks in advance for any help you can provide!
 
Assuming your LARGE formulas start in B11, the array formula is:

=INDEX(B:B,SMALL(IF($A$3:$A$8=B11,ROW($A$3:$A$8)),COUNTIF(B$11:B11,B11)))


Rememebr to confirm using Ctrl+Shift+Enter, not just Enter. Then, copy down formula as needed.
 
Also, for ease in formula writing so you don't have to keep writing things, you could:

=LARGE($A$3:$A$8,ROW(A1))

and copy down, rather than having to change the last argument each time.
 
Thanks Luke, that worked perfectly for the example I provided. My practical use is a bit mor complicated, but I'll fool with it on Monday and keep trying to adapt it.


I'm actually trying to search numbers in AR$23:AR$64 and return the corresponding name from BM$23:BM$64. My LARGE formulas start in B34.


This adaptation is returning a name (not an error, so that's a start!)...


=INDEX(BM:BM,SMALL(IF(AR$23:AR$64=B34,ROW(AR$23:AR$64)),COUNTIF(B$34:B34,B34)))


...But it's the wrong name. If you can figure out what I am trying to say than it'd be great to see your re-write. I also understand the benefits of figuring it out on my own :)
 
And that LARGE re-write works awesome!! I don't understand how it works, but it does. It even works where I have data in A1?!? I love it!
 
Your formula looks correct. Since you're not getting an error, sounds like you confirmed it as an array. When I setup my workbook to match your current layout, everything works correct. Are you sure it's the wrong name?


When the blog is working again, you can check out the full article on this type of formula here:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


Until then, here's another explanation:

http://chandoo.org/forums/topic/lookup-1


The LARGE formula rewrite uses the ROW function. This function doesn't care what's in the cell being reference, it just returns the row number of the cell. So, ROW(A1) = 1. Since it's a relative reference, when you copy down, it becomes ROW(A2), which evaluates to 2. the ROW and COLUMN functions are very handy when you need a formula to increment something.
 
Here is a note that may help: The unexpected result I'm getting is the value of BM34. I also get a #NUM! error when I copy this formula down in my actual application. When I copied your formula down in my demo table it worked great and changed dynamically.
 
Even more strange...perhaps try using a smaller range to start with, like AR23:AR30. And/or try the evaluate formula button?
 
OK I have it working perfect now by remembering to Shift+Ctrl+Enter. Sorry for missing the obvious. That is an amazing couple of formulas! Thanks for taking the time to help!
 
You must log in or register to reply here.
Back
Top