Can Vlookup return multiple values?

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • If values are repeated in the left most column of my vlookup table, how do I get Excel to return differing values from other columns? I'm rank ordering golfers according to score, lowest score (ranks 1st) to highest score. The Rank is the Vlookup first column, and if two golfers scores are the same, their rank is the same, yet the Vlookup function repeatedly returns only the first golfer's name. Is there a means for returning the name of the second/third/fourth etc golfer which has the same rank?


    Example: Random golfers names, scores and ranking


    A B C
    1 Rank Score Name
    2 2 74 Adams
    3 5 76 Anderson
    4 2 74 Christopher
    5 1 72 Martin
    6 2 74 Smith


    Without using Sort function, since I would like the spreadsheet to be static, I wish to place golfers in rank order as follows on another section of the spreadsheet:


    A B C
    10 Rank Score Name
    11 1 72 Martin
    12 2 74 Adams
    13 2 74 Christopher
    14 2 74 Smith
    15 5 76 Anderson


    Formulas for Rank and Score are fine, but formula for Name returns Adams for each occurance of Rank "2", when need second and third occurrances of Rank "2" to return Christopher and then Smith.


    Current formula being used which doesn't accomplish objective:


    =VLOOKUP(A11,A2:C6,3,FALSE)


    (When I went to post, spacing between columns went away; hope you can read.)


    Thanks in advance for any assistance.

  • Re: Can Vlookup return multiple values?


    Maxi,


    As per your request, here's an explanation as to how the formula works...


    If we take a look at the following formula...


    =INDEX(A$2:A$6,MATCH(SMALL($B$2:$B$6+ROW($B$2:$B$6)/10^10,ROWS(A$11:A11)),$B$2:$B$6+ROW($B$2:$B$6)/10^10,0))


    …here's how it breaks down:


    ROWS(A$11:A11) evaluates to 1


    $B$2:$B$6 returns the following array of values...


    74
    76
    74
    72
    74


    ROW($B$2:$B$6)/10^10 returns the following array values...


    2/10^10 ---> 0.0000000002
    3/10^10 ---> 0.0000000003
    4/10^10 ---> 0.0000000004
    5/10^10 ---> 0.0000000005
    6/10^10 ---> 0.0000000006


    $B$2:$B$6+ROW($B$2:$B$6)/10^10 returns the following array of values...


    74.0000000002
    76.0000000003
    74.0000000004
    72.0000000005
    74.0000000006


    As you can see, the second part...


    ROW($B$2:$B$6)/10^10


    ...is used to create unique values. Here are the evaluations that take place...


    =INDEX({2;5;2;1;2},MATCH(SMALL({74.0000000002;76.0000000003;74.0000000004;72.0000000005;74.0000000006},1),{74.0000000002;76.0000000003;74.0000000004;72.0000000005;74.0000000006},0))



    ...which evaluates to...


    =INDEX({2;5;2;1;2},MATCH(72.0000000005,{74.0000000002;76.0000000003;74.0000000004;72.0000000005;74.0000000006},0))


    ...which evaluates to...


    =INDEX({2;5;2;1;2},4)


    ...and returns 1. Then, when the formula is copied down...


    ROWS(A$11:A11)


    ...changes to...


    ROWS(A$11:A12)


    ...and returns 2. This number is then used by the SMALL function to return the 2nd smallest value, and so on...

  • Re: Can Vlookup return multiple values?


    Thanks so much for your help. Not being familiar with the functions you have used, and having found Excel Help unhelpful, might you explain, in simple terms what each step of the formula is doing. I can see that the formula generates the correct results; I just don't know how it is doing so and my actual Excel file is far more complicated than the example I used, and I will need to use the same type of formula elsewhere. Thanks in advance.

  • Re: Can Vlookup return multiple values?


    Maxi,


    The explanation I provided you with is based on the example in the sample file originally attached. Note that I revised it slightly after I uploaded it, so hopefully you have the subsequent version where the formulas are entered in A11:C15.

  • Re: Can Vlookup return multiple values?


    As Domenic was posting the answer to my question regarding how the formula worked, I was in the throws of creating a spreadsheet which would better explain my exact problem. It is attached, and I have yet to attempt to use the Domenic formula for my exact needs but will attempt to do so. If it will not serve me, however, it would be good to know. Thanks.

  • Re: Can Vlookup return multiple values?


    Enter the following formula in B70, copy down and to your other columns...


    =INDEX(B$15:B$57,MATCH(SMALL($F$15:$F$57+ROW($F$15:$F$57)/10^10,ROWS(B$70:B70)),$F$15:$F$57+ROW($F$15:$F$57)/10^10,0))


    ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.


    Hope this helps!

  • Re: Can Vlookup return multiple values?


    Domenic --
    Thanks so much!! Now, one more question: Obviously each time the schedule I've outline is used, there will be a different number of players, and I've found if I have any blank rows in the upper schedule, the formula which creates the lower schedule does not work. Any way that can be resolved?
    Maxi

  • Re: Can Vlookup return multiple values?


    Try the following formula...


    =INDEX(B$15:B$57,MATCH(SMALL(IF($F$15:$F$57<>"",$F$15:$F$57+ROW($F$15:$F$57)/10^10),ROWS(B$70:B70)),$F$15:$F$57+ROW($F$15:$F$57)/10^10,0))


    ...confirmed with CONTROL+SHIFT+ENTER. Note that you'll get #NUM! when you have empty rows in your source table. You may want to use conditional formatting to hide these error values when they occur. Post back if you need help with this.

  • Re: Can Vlookup return multiple values?


    Dominic --
    Thank you; thank you; thank you! This was the perfect solution for my problem and the Indo Tournament portion of my golf match workbook. Sorry to be so slow to respond; Christmas, etc.


    Next, I don't know if there is a solution for my next problem, however, but I'll throw it out and see what you think.


    In golf tournaments, players are usually only allowed to win one prize. Therefore, if their scores (gross and net) would place in both the Gross and Net divisions, the player is given what is called the "Highest Finish" or most money of the two positions. He then obviously does not win in the other division, freeing up a "Place" for another player. Soooo, lets say a player wins 1st Place in Gross and 3rd Place in Net. He is awarded the 1st Place prize for gross (assuming it is the most prize money), but receives nothing for his 3rd place finish in net. (Also, Gross finish takes precedence over Net, i.e. had he won 1st place in both divisions, he would have been awarded 1st in Gross and nothing in Net, assuming 1st place prize money in each division is the same.) Obviously this is done to create the greatest number of winners.


    Anyway, what I need to create is a "Best Finish" results summary which would systematically look at each winner sequentially, award the highest available prize won, then go to the next winner, do the same, etc. until all prizes have been awarded and no one has received more than one prize, yet all have received the Highest Award to which they could be entitled. If this is beyond the Excel realm, I understand. But I figured it can't hurt to ask.

  • Re: Can Vlookup return multiple values?


    D--
    Wow. Blow me away.


    This is going to take me a bit to figure out. Did you just "whip this out?" I'm going to nominate you for the Excel-Einstein award.


    Apparently iterations are not required, as I assumed. I'll get back once I've figured everything out. But in the mean time, thanks a gazillion.


    Maxi

  • Re: Can Vlookup return multiple values?


    Quote from Maxi

    D--
    Wow. Blow me away.


    LOL


    Quote

    Did you just "whip this out?"


    I wish... I had to think about it for a bit... :)


    Quote

    Apparently iterations are not required, as I assumed. I'll get back once I've figured everything out.


    Are you saying that you've already tested it out?


    Quote

    But in the mean time, thanks a gazillion.


    You're very welcome!

  • Re: Can Vlookup return multiple values?


    No; I've not tested it yet for my particular case.


    FYI, Net scores are always lower than Gross scores in golf. The Gross score is how many strokes the round actually takes a person; Net score = Gross Score minus handicap. So, a gross score of 85 for a 15 handicapper results in a Net score of 70.


    My situation will even get trickier than I've explained since I've written a program which pays various places (from 1 to 15 places in each division) for Gross - 18 holes, Gross - Front 9, Gross - Back 9, Net - 18 holes, Net Front 9 and Net Back 9.


    Now we've got a story problem. Anyway, I'll get back once I've figured out what you've already created. And man, am I impressed!

  • Re: Can Vlookup return multiple values?


    D --


    Oh, and one more thing FYI. The previous version of Golf Match, the Excel program I've created and hope to sell (zero so far), may be found at http://www.golfmatch.us. This latest Indo-Tourney thing I'm working on is for a next version. Thanks, thanks, thanks, thanks, thanks, thanks, thanks.


    I'll post again once I've figured out what you've created (may be a few days; busy with other stuff!) And if I failed to mention it, thanks.
    Maxi

  • Re: Can Vlookup return multiple values?


    Quote from Maxi

    I've plugged in more reasonable values with each player having a handicap which then determines his net score, as you will see.


    Yeah, I know that the numbers I plugged in may not have made quite sense, but I used them for testing purposes.


    Quote

    I've also plugged in a vlookup formula to return each winner's score below. (Don't know how to do the dough yet.)


    Don't worry about the dough just yet. That will be the easy part once we take care of the ranking.


    Quote

    Anyway, in the below example, Rich Anderson should be receiving 1st place in Net, since if he ties for first in Gross, he and Christopher will be splitting the sum of $12,000 + $6000, which would give him $18,000 / 2 = $9000, whereas Anderson's 67 should put him alone in first place in the Net division which would win $12,000.


    So I take it that Scott Butler, who's next in the ranking order and ranks 2nd gets $3,000. What if Jeff Cook is tied with Scott with a Gross Score of 76 and ranks 3rd? Also, what if, instead, Jeff keeps his score of 78?


    I take it, the same princples would apply to Net Scores. For example, if some else were tied for 1st place with Rich Anderson, they'd be splitting the sum of $12,000 + $8,000, which would give them $10,000 each. And, in this case, Rich Anderson would be comparing his winnings in Gross Score ($9,000) with his winnings in Net Score ($10,000), and still receive first place in Net, right?


    Also, what if six people are tied for 1st place in Gross and none have placed in Net? And what happens to those who rank 2nd and 3rd?

  • Re: Can Vlookup return multiple values?


    Hi Dom --
    First of all, I'm slow. I didn't know you'd responded to my private message; I assumed I'd received an e-mail notice once you responded, but I didn't. And OK, I'll try to keep it on the board.


    Attached is our back and forth winnings2.xls file with a few more clarifications you'll understand I hope. But first read what I've written below.



    All who tied for 1st split 1st, 2nd and 3rd prize: $12,000 + $6000 + $3000 = $21000 / 6 = $3500 each. And those who rank 2nd and 3rd in Net win nothing (because they really place 7th and 8th).


    OK Next: Pursuant to your question to explain precedence for Highest Finish (the terminology should say Highest $ Finish) here goes --


    1) Highest available prize is first precedent. (if 1st Gross pays $10,000 and 1st Net pays $12,000, and one person places 1st in both categories, he would win 1st Place Net and receive 1st Place Net prize money. If the next highest prize is 1st Gross, then next lowest Gross score wins 1st Gross prize.)


    2) If places (1st, 2nd, 3rd etc) pay the same in both gross and net, then Gross finish takes precedence over Net finish.


    (e.g. in our Winnings2.xls worksheet, if Anderson HAD NOT been tied by Christopher in Gross, Anderson would have placed 1st in BOTH Gross and Net divisions, yet would have won 1st Place Gross and been paid 1st Place money in Gross and nothing in Net.)


    3) Next, if 1st 2nd 3rd etc in Gross and Net pay the same amount, once 1st Place Gross is determined, 1st Place Net is then the next highest available prize, so the lowest net score is next determined and awarded this prize. If no ties exist, 1st Net is awarded, and then the next precedent would be back to determine 2nd Gross. If ties exist, however: a) if none of those who tie would place in Gross, how ever many tie split total amount due that many places (4 tie = equally split 1st, 2nd, 3rd and 4th place dough) b) if one of those who tie for 1st Net would win Gross 2nd alone which would pay more than 1/4 of sum of 1st thru 4th money in net, that person receives Gross 2nd (his highest finish available) and the other 3 then split the sum of 1st, 2nd and 3rd Net.


    So, we have now awarded Gross: 1st, 2nd; and in Net: 1st, 2nd, 3rd (who all tied). Next highest available prize is 3rd Gross, so the next lowest Gross score wins 3rd place. But again if there are ties for 3rd Place Gross, and if one or more of those who tie would place in the Net division and win more than the winnings they'd receive by splitting 3rd, 4th, 5th whatever places etc in the gross division, they then go back to the Net payout.


    Another factor which can serve to further confuse the Highest $ Finish determination is the fact that most tournaments pay more Net places than Gross places, since the actual Gross “field,” in reality, is much smaller than the Net “field.” Although all players are considered “entered” in both the Gross and Net divisions, only the best players in the field truly have a chance in the Gross division. (e.g. A 24 handicapper is never going to shoot a score which will earn him a “place” in the gross (handicap doesn’t count) division. See what I mean?)


    Clear as mud? Whistle if I've not made myself clear. Hopefully the attached file will again help.


    And once again, thank you thank you thank you in advance.


    Maxi


    PS Still studying my array formulas. They make me feel like a real dullard.

  • Re: Can Vlookup return multiple values?


    Hi Domenic,


    I see this post is dated 9 years ago but I hope you are still around.


    I have found the formula you listed very helpful.


    I just have a questions if that is ok.


    I have noticed that the values returned are in reverse alphabetical order. Is there anyway to change this?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!