fbpx
Search
Close this search box.

Getting the 2nd matching value from a list using VLOOKUP formula

Share

Facebook
Twitter
LinkedIn
This article is part of our VLOOKUP Week. Read more.

Situation

We know that VLOOKUP formula is useful to fetch the first matching item from a list. So what would you do if you need 2nd (or 3rd etc.) matching item from a list?

For eg. If you have below data, and you want to find out how much sales John made 2nd time, then VLOOKUP formula becomes quite useless. Or is it?!?

Data:

Data for this Example -Getting the 2nd matching item from a list using VLOOKUP formula

Solution

A simple solution to this problem would be sorting our data on sales person’s name. That way all Johns would line up one beneath another. And we just have to find the first John’s position and add 1 to it to get to 2nd occurrence. Like this =MATCH("John", C5:C17, 0) + 1

But sorting is not an option all the time. So there should be a better way to do this?

Well, there is. We just add a helper column before the sales person name and fill it with sales-person’s name & occurrence. (see the below data table).

For this we can use COUNTIF() Formula, like this: =C5&COUNTIF($C$5:C5,C5). Notice the $C$5:C5?, well the mix of absolute & relative references does the trick here and gets John1, John2… etc.

Now, to lookup 2nd occurance of John, all we do is, simply write =VLOOKUP("John2",...) and we are done.

Data for this Example -Getting the 2nd matching item from a list using VLOOKUP formula

Sample File

Download Example File – Getting the 2nd matching item from a list using VLOOKUP formula

The file includes few examples on how to fetch 2nd, 3rd etc. matches using lookup formulas. It also has some interesting (and challenging) home work for you. Download & play with it.

Similar Tips

VLOOKUP Week @ Chandoo.org - Learn tips on lookup formulas in Excel

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

58 Responses to “Getting the 2nd matching value from a list using VLOOKUP formula”

  1. sam says:

    4. Is there way to fetch 2nd sales amount for Josh without using the helper column (B5:B17) ?
    Select Cell I6
    Define a name : cName = If(Person=$H6,Row(Person)-Row($C$3))
    In cell I6 Type = Index(Sales,Small(cName,2))
    This will return the 2nd instance.

  2. Luke M says:

    Essentially the same as sam's idea, but you could combine it all into one formula if you use an array formula:
    =INDEX(D:D,SMALL(IF(B5:B17="John",ROW(B5:B17)),2))
    You can, of course, make these formulas dynamic by replacing the text "John" and the number 2 with cell references.

  3. Stumped! I got the first one, but can figure out the other ones! 🙁 Any place to find the answers?

  4. Tristan says:

    Here is what I have:
    1)
    =SUMPRODUCT((MAX((C5:C17="Jamie")*ROW(C5:C17))=(C5:C17="Jamie")*ROW(C5:C17))*1,E5:E17)
    2)
    =SUMPRODUCT(MAX((C5:C17="John")*(E5:E17)))
    3)
    =COUNTIF(INDIRECT("C5:C"&SUMPRODUCT((MAX((C5:C17="John")*(E5:E17))=((C5:C17="John")*(E5:E17)))*1,ROW(C5:C17))),"John")
    4)
    =INDIRECT("E"&SMALL(INDEX(((C5:C17="Josh")*ROW(C5:C17))+((C5:C17"Josh")*10^8),0,0),2))
    The matrix formula Luke M has is definitely simpler. I was just trying to avoid a matrix.
    I did come up with a more complicated matrix formula for #4. I was thinking of how I could make a matrix form of a running total of the number of occurrences of value. Maybe it will help me some other time.
    Here it is:
    {=OFFSET($E$4,MATCH(2,MMULT(IF(ROW(OFFSET(INDIRECT("A1"),0,0,ROWS(C5:C17),ROWS(C5:C17)))>=COLUMN(OFFSET(INDIRECT("A1"),0,0,ROWS(C5:C17),ROWS(C5:C17))),1,0),INDEX((C5:C17="Josh")*1,0,0)),0),0)}

  5. Tristan says:

    I had a strong desire to use a vlookup on problem 4. Here is the matrix formula I worked out for doing that.

    {=VLOOKUP(2,MMULT(IF(ROW(OFFSET(INDIRECT("A1"),0,0,ROWS(C5:C17),ROWS(C5:C17)))>=COLUMN(OFFSET(INDIRECT("A1"),0,0,ROWS(C5:C17),ROWS(C5:C17))),1,0),IF(COLUMN(C5:F17)=COLUMN(C5:C17),((C5:C17=”Josh”)*1),0))+IF(COLUMN(C5:F17)=COLUMN(C5:C17),0,C5:F17),3,FALSE)}

    A few named formulas and this can be simplified.

  6. chrisham says:

    All Array Formulas
    1) =INDEX(NetSales,LARGE(IF(SalesPerson="Jamie",ROW(SalesPerson)-MIN(ROW(SalesPerson))+1),1))
    2) =MAX(IF(SalesPerson="John",NetSales))
    3) ="John"&COUNTIF(C5:INDIRECT("C"&MAX(IF(IF(SalesPerson="John",NetSales)=MAX(SalesPerson="John",NetSales),ROW(NetSales)))),"John")
    4) =INDEX(NetSales,SMALL(IF(SalesPerson="John",ROW(SalesPerson)-MIN(ROW(SalesPerson))+1),2))

  7. Chandoo says:

    @All.. good solutions.

    @Perry: Here is a set of solutions. You can obviously improve or comeup with diff. approaches. http://chandoo.org/img/f/vw/vlookup-2nd-value-instructor-copy.xls

    @Tristan: Can you explain =INDIRECT(“E”&SMALL(INDEX(((C5:C17=”Josh”)*ROW(C5:C17))+((C5:C17?Josh”)*10^8),0,0),2)) for us?

  8. JuanCarlos says:

    It's an awesome job!!!... thank you

  9. Tristan says:

    I will endeavor to do so, Chandoo.
    First off, I just now noticed that there is a not equal sign missing between the second C5:C17 and “Josh” (It must not render incorrectly)
    So here is the idea behind how it works:
    The overall idea is to return the row of the second occurrence of “Josh”
    First the array is checked to see it if an item is equal to “Josh” (C5:C17=”Josh”) this generates and array of True and False
    Then multiply the true and false array by the array of row numbers (Row(C5:C17)) this transforms the array into an array of zeros and row numbers where the value is “Josh”
    The problem here is that there are an unknown number of zeros in the array. This causes the small function to fail. So I want to replace the zeros with values that are larger than any encountered as a row.
    This I did by adding the array of items not equal to “Josh” (C5:C17 not equal “Josh”), multiplied by 10^8
    This results in an array of large numbers (where the value is not equal to “Josh”) and row numbers where the value is equal to “Josh”
    The index function causes everything to be treated as an array, and then small picks the second smallest number which is the row number of the second occurrence of “Josh”

    • Christopher says:

      Can you get the this to work but on a different sheet in the same file?

      =INDIRECT("H"&SMALL(INDEX((('Above Ground'!B6:B800='Pipe Stringing_Lowering'!C8)*ROW('Above Ground'!B6:B800))+(('Above Ground'!B6:B800<>'Pipe Stringing_Lowering'!C8)*10^8),0,0),2))

      I get it to work in the sheet I am looking up the value but I can't find a way to get this to work if I want it on another sheet.

      Help would be great!

      Thanks!

  10. Alejandro says:

    Thank you for the post, it actually helped me to make an auto-fill list, depending on an specific value on a cell. Thanks again!

  11. Rahul says:

    Dear Chandoo:

    Can you explain what exactly happens with the formula you have written as answer for the 2nd question in the exercise, i.e.:
    = SUMPRODUCT(MAX(($C$5:$C$17="John")*($E$5:$E$17))).

  12. Rahul says:

    Also in the answer of the 3rd question, how does Countif arrives at the right answer? Does Excel check the Countif first or the calculates Offset first?

  13. Rahul says:

    sorry, my bad. I was little confused in the 3rd answer, i got it now. Will wait for your reply for the 2nd.

  14. Chandoo says:

    @Rahul... The sumproduct formula checks for all records and returns true where person=John. This gets multiplied to the actual sales values. Since true is 1 and false is 0, we get a bunch of values with actual sales for john and rest as zeros. The max, then returns the maximum value among these. Refer to this article for a tutorial on sumproduct formula: http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

  15. Chandoo says:

    @Tristan: Very clever approach. Thanks for teaching us this technique. Here is a donut for you 🙂

  16. VEMULA SHANKAR says:

    DISCOUNT -7% & -5% = ?
    MRP
    EX 880
    ( Two discount in one MRP - FIRSR SEVEN PERCENTAGE & AFTER 5% DISCOUNT , HOW CAN I GIVE THE FORMULA FOR ROWS IN NEXT CLOUMN DIRECT VALUE?

  17. V S VENKATRAMAN says:

    Dear All

    I think I need to give more time to catch up with you all... thanks to guru chandoo... i am confident that i can learn with these techniques.... thanks to all

    V S Venkatraman

  18. ASHISH JAIN says:

    Hi Chandoo,

    It was nice reading your blog. I got introduced to your blog by my brother 'Ayush Jain'. Really good good stuff. Very innovative and different approach.

    Regards
    Ashish Jain
    http://www.excelitems.com
    http://www.openexcel.com

  19. Chandont says:

    When you are posting examples, you need to post the column letters and row numbers. Otherwise, the reader has no idea to which cells a formula is referencing. What makes your example above especially egregious is that you skipped an arbitrary number of rows and columns before the data table. Therefore, no one could possibly know which cell is C5.

    Yeah, I know we can download the spreadsheet. That's not the point.

  20. Stephen says:

    You could try either of these links if you want a custom function (thanks to ozgrid!)
    http://www.ozgrid.com/Excel/find-nth.htm
    http://www.ozgrid.com/VBA/TwoColLkUp.htm

  21. Raghuram Bhat says:

    Hi All,

    I was preparing Birthday Reminder Sheet and I was using VLOOKUP. If the date of birth and month of a person matches with todays date and month(dd-mm), then their name should display. I used simple VLOOKUP formula
    =VLOOKUP((TEXT(TODAY(),"dd-mm")),G2:H165,2,FALSE)
    However, if there is more than 1 Birthday's then this formula doesnt work.:(
    Kindly resolve this and provide me solution.

    Thanks in advance.

    Raghuram Bhat
    raghurambhatg@gmail.com

  22. ExcelPlayer says:

    Thanks Heaps for this solution, allows me to look up values based on the first return so that each return after is relevant.

    To the complainers and wingers, if you dont understand how the data is used in the downloaded spreadsheet, look up what you are not understanding. Stop bagging someone who is trying to help you.

    Cheers

  23. VENKY says:

    Just ON-TIME when i needed it the most.... No Words to thank

  24. Mark Domingo says:

    Hi!

    There's a more simple way to solve above problem, by only using vlookup and countif, but it need extra column

    Sample.

    Row     column A         Column B            column C
      1      Name_C             Name                  Score
      2       mark1               Mark                       3
      3       Jay1                  Jay                         4
      4       Mark2               Mark                       5
      5
      6       mark                   3                   1st value
      7       mark                   5                   2d value

    A2 formula = b2 & countif(b$1:b2,b2)
    to be copied until a4

    b6 formula = vlookup(a6&1,$a$2:$c$4,3,false)
    b7 formula = vlookup(a7&2,$a$2:$c$4,3,false)

    Done!
     

    • Chris Shelton says:

      Use a combination of the Vlookup function, Match function, and the Offset function.

      Example:

      =VLOOKUP(lookup_value,OFFSET('sheet_name'!$A$1:$C$2000,MATCH(lookup_value,'sheet_name'!$A:$A,0)+1,0),2,FALSE)

      What the does is:

      1.  Returns the first occurrece of the lookup value as an integer and adds one (Match function)
      2.  Takes the lookup_range and offsets it by the the total number of rows returned in step 1 (Offset function returns new range)
      3.  Runs a Vlookup using the new range and the vlookup value

      Hope this helps.

  25. Anup says:

    Awesome approach. Solved my problem perfectly.
    Thanks Much!!!

  26. Jalindah Woods says:

    Good stuff!  I have a problem.  I'm trying to populate a field based on an occurence of a value in a range and it works fine.  Shown below:
    =IF(ISERROR(MATCH(ROUND($E$3,10),'2013-TaskList'!$J$4:$J$5004,0)),"",INDEX('2013-TaskList'!$K$4:$K$5004,MATCH(ROUND($E$3,10),'2013-TaskList'!$J$4:$J$5004,0)))

    But I also want to populate the 2nd, 3rd, and 4th occurence of the same value in the given range and its not working.  I need to nest it into the current formula I have but I'm unsuccessful in doing so.

    Can anyone help???

    Thanks.

  27. mahesh says:

    How do I CONCATENATE 1st, 2nd .. values

  28. Heinrich says:

    What also works very well is to add the returned value  in the same row. This will allow you to compile a list off the result of one vlookup. Choose the one you wish to see, and then use another vlookup to return those values.

  29. Nazim says:

    Hi all, 
     
     
    I am having difficulty wrapping my head around this formula:
     
    =COUNTIF(INDIRECT(“C5:C”&SUMPRODUCT((MAX((C5:C17=”John”)*(E5:E17))=((C5:C17=”John”)*(E5:E17)))*1,ROW(C5:C17))),”John”)
     
    Could someone please explain this.....
     
    I tried to go different way: through MATCH function to determine the position of John's max sale. But all it gives to me is 13, instead of 4. If I have an following array :
    {0;1088;0;0;0;1540;0;0;0;726;0;0;2682}, how can I make it look like this {1088;1540;726;2682}, in other words how can I get rid of zeros(false) and keep the order of non-zero(true) values. 
    My formula was like: =match(max((salesman="john")*(sales));((salesman="john")*(sales));0)
     
    Thank you.
     
     
     
     
     
     

  30. karthik says:

    I am trying to lookup the sequence of values using this MATCH INDEX formula. I trying to find the second and subsequent values but i can"t get it. Please help me.
    =IFERROR(INDEX('2013 MBR'!M3:M6000,MATCH("1"&$F$2&"Day",'2013 MBR'!$S$3:$S$6000&'2013 MBR'!$Z$3:$Z$6000&'2013 MBR'!$AA$3:$AA$6000,0)),IFERROR(INDEX('2013 MBR'!M3:M6000,MATCH("1"&$F$2&"Day",'2013 MBR'!$S$3:$S$6000&'2013 MBR'!$AC$3:$AC$6000&'2013 MBR'!$AD$3:$AD$6000,0)),IFERROR(INDEX('2013 MBR'!M3:M6000,MATCH("1"&$F$2&"Day",'2013 MBR'!$S$3:$S$6000&'2013 MBR'!$AF$3:$AF$6000&'2013 MBR'!$AG$3:$AG$6000,0)),IFERROR(INDEX('2013 MBR'!M3:M6000,MATCH("1"&$F$2&"Day",'2013 MBR'!$S$3:$S$6000&'2013 MBR'!$AI$3:$AI$6000&'2013 MBR'!$AJ$3:$AJ$6000,0)),IFERROR(INDEX('2013 MBR'!M3:M6000,MATCH("1"&$F$2&"Day",'2013 MBR'!$S$3:$S$6000&'2013 MBR'!$AL$3:$AL$6000&'2013 MBR'!$AM$3:$AM$6000,0)),IFERROR(INDEX('2013 MBR'!M3:M6000,MATCH("1"&$F$2&"Day",'2013 MBR'!$S$3:$S$6000&'2013 MBR'!$AO$3:$AO$6000&'2013 MBR'!$AP$3:$AP$6000,0)),"NA"))))))
     
    Thanks
    karthik

  31. Rahul says:

    Hi Guys

    1 question

    A vlookup when done with a wild card for example =VLOOKUP(A1&"*",C:C,1,0) always returns the exact match first, how can you get the 2nd match with extensions (for which we use wild card) first without sorting the list.

    Thanks

    Rahul

    • sam says:

      my question is :- If one sku is sitting on multiple locations than how we can find all locations with the help of Vlookup
      I used =VLookup($c18,list2,2,false)

      In column 2 i have multiple location for sku but i m getting only one in Vlookup, How i can get all location of this sku.

      Please advise
      Thanks & regards

  32. varsha says:

    I want to lookup completion date for X employee in a sheet wherein multiple entries are there, how to Pick up the Latest completion date for the employee

  33. ahhhmed says:

    Why can't we add another helper column so that we can lookup
    a diiferent value?

  34. Mithun K says:

    Dear all,

    I am average user of MS Excel. I just read all formulae above but I couldn't understand & I have same problem too. I wanted to arrange two sheets on single sheet to refer data. On both sheets common column is "name of item". but in that few items are repeated. thus when I apply vlookup on it, it shows same value for repeated items. whereas it suppose to show different values.

    which formula should I apply?

    Please help!

    Thank you!

  35. Gabriel Q. says:

    Great solution, and yet so simple to replicate! Thanks a lot for such a useful tip! Cheers.

  36. Fox says:

    Thanks for this! Worked a treat!

  37. COCO says:

    Thanks for your website and truely appreciate your sharing. Help me a lots ( Both Stat and Excel Function)

    Cheers~

  38. giz a job in reading berks says:

    Of course sometimes it might be better to use a pivot table.
    Generally, I now prefer to use a nested match within a index as it is more flexible.

    I generally find countif(s) and sumif(s) are handy and have my own user defined functions for MAXifs, MINifs, which could be used to find earliest or latest/ largest or smallest entries. I have also combined sumifs to do a kind of averageifs for example if an item is sold at different prices in different volumes working out the average price per unit.

  39. Wei says:

    Here is the thing. It worked well with the text (John and etc,) and if the value is number. It wont work.

    200 A
    200 B
    200 C
    200 D

    how to find the following with formula
    200 A B C D

  40. Ekta Singh says:

    Hi, can u pls upload a video so that we can understand it more

  41. Dilip says:

    Excellent. You are the best dear.

  42. U4 says:

    Chandoo please unravel this formula for me. Thanks. =INDEX($E$5:$E$17,SUMPRODUCT(SMALL(($C$5:$C$17="Josh")*(ROW($C$5:$C$17)-4),2+COUNTIF($C$5:$C$17,"Josh"))))

  43. Alex says:

    So I've used this guide (really good by the way) to allow me to return a cell from a VLOOKUP based upon selections from 2 drop downs.

    I want to be able to drag the code down and return the 1, 2, 3, 4 etc cell until i have all of the cells listed, then i have a hlookup returning the data relating to the cells.

    How do I make my VLOOKUP formula automatically increase the number that it's searching for as i drag it down the column?

    I've tried this, it just returns the same cell over and over and I can't think how to increment the 1 up:

    =VLOOKUP($C$2&$C$3&1,'Raw Data'!$M$2:$N$499,2,FALSE)

    Hope someone can help!

    Alex

  44. Doug Meadows says:

    I have been looking for this answer for a very long time. I use vloolup all the time but have been stumped with multi row data. All the previous solutions I have found out there where to complicated. This solution is so simple and is amazing. Thanks

    Doug Meadows

  45. Niki says:

    Hey - relating to the original question here. Is there a way of returning multiple matches for a single date rather than for a list of names using vlookup?

    eg

    Date Price
    1/5/16 25
    1/5/16 30
    3/10/16 35

    instead of John and John, an equivalent for dates? eg: 1/5/16 a and 1/5/16 b or something to separate the two dates and return 25 and 30 respectfully?

    many thanks!

  46. keval says:

    I want to know that i have one invoice having 2 lots.for instance.
    Invoice no Lot no
    INV17038 421C22
    INV17038 421C23

    Suppose i put INV17038 I Should be able to get all lots in connection to one invoice. which formula need to be putted

  47. Sandeep Kothari says:

    Chandoo, u r simply gr8!
    U make things look so simple.

  48. Mark says:

    I have something similar to below:-
    A B C
    101 Bill Fish
    101 Bill Chips
    103 Joe Chicken
    104 Don Pizza
    103 Joe Prawn
    101 Bill Peas
    107 Sam Pie
    103 Joe Curry
    101 B1ll Gravy

    search Column (A) above and produce below from Colomn (C) in a separate sheet
    A B C D E
    101 Fish Chips Peas Gravy
    103 Chicken Prawn Curry
    104 Pizza
    107 Pie

    Please can you help me ?

Leave a Reply