Announcement

Collapse
No announcement yet.

How to use VLOOKUP when the same value is repeated....

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to use VLOOKUP when the same value is repeated....



    Hello:

    I have a list of store numbers and their corresponding employee roster. Each store number has between 10-15 employees.

    How do I use the VLOOKUP function to return all employee names at each store number?

    For example, my spreadsheet looks similar to this:
    Store # (Cell A1) Employee Name (Cell B1) Employee ID (Cell C1)
    8100 Joshua 1234
    8100 Tim 5678
    8100 John 2124
    8100 Steven 5471
    8200 Nicole 1114
    8200 Stacey 8482
    8200 Mary 3759
    I have created a drop down list with all store numbers on a separate tab within the same workbook. When I select a store number from the drop down I need the above table to be returned for only the store number I select.

    Is this possible?

    I have tried so many things but it only returns the first occurrence multiple times. In my example, when I select 8100 from the drop down – Joshua is returned multiple times but never returns Tim.

    I hope this make sense.

    Thank you,

    Joshua

  • #2
    Re: How to use VLOOKUP when the same value is repeated....

    Joshua - you can do this with a formula, but why not use AutoFilter which requires no work at all?

    Comment


    • #3
      Re: How to use VLOOKUP when the same value is repeated....

      Hello StephenR:

      By using AutoFilter, will it generate my desired results? I want to be able to use the drop down list of store numbers to automatically return the employees name in a separate table at the bottom of the spreadsheet.

      In other words, when I select store # 8100 from the drop down – how do I get all the employee names associated with that store number listed?

      If you can show me how to do this - I will test it.

      Thank you,

      Joshua

      Comment


      • #4
        Re: How to use VLOOKUP when the same value is repeated....

        Well Autofilter has built in drop downs so you don't need to add your own. Have a go and see.

        Comment


        • #5
          Re: How to use VLOOKUP when the same value is repeated....

          I am creating a dashboard. I require my own drop down as the users of this dashboard will not have access to my master employee file. As a result, AutoFilter will not work in my situation.

          Any other suggestions?

          Thanks for the help.

          Joshua

          Comment


          • #6
            Re: How to use VLOOKUP when the same value is repeated....

            To clarify, I need to look up the value in column A (which contains the same store # multiple times) and return all the associated employee names for all occurrences.

            The value to be searched for in column A will depend on what store # is selected from the drop down menu I have created.

            User selects store # from the drop down, Excel does a search based on the store # selected against column A in my master employee sheet and returns all the employee names associated with that store #.

            Joshua

            Comment


            • #7
              Re: How to use VLOOKUP when the same value is repeated....

              If your data are in A1:C8, your store is in F1 and results in G1 and down and across, put this in G1 and copy as necessary.

              =IF(ROWS(G$1:G1)<=COUNTIF($A$2:$A$8,$F$1),INDEX(B$2:B$8,SMALL(IF($A$2:$A$8=$F$1,ROW($A$2:$A$8)-ROW($A$2)+1),ROWS(G$1:G1)),1),"")

              Confirm with ctrl+shift+enter.

              Adjust ranges necessary. If you can't do that, please post a workbook.

              Comment


              • #8
                Re: How to use VLOOKUP when the same value is repeated....

                I tried but didn’t get this to work. I have attached the worksheet.

                Any questions, let me know.

                When you select the store number from the drop down, I need the employee names from the “Master Sheet” tab to be populated on the “User Information” tab.

                Thanks,

                Joshua
                Attached Files

                Comment


                • #9
                  Re: How to use VLOOKUP when the same value is repeated....

                  Put this in A5 and copy down for employee names

                  =IF(ROWS(A$5:A5)<=COUNTIF('Employee Master Sheet'!$A$3:$A$1139,$B$2),INDEX('Employee Master Sheet'!B$3:B$1139,SMALL(IF('Employee Master Sheet'!$A$3:$A$1139=$B$2,ROW('Employee Master Sheet'!$A$3:$A$1139)-ROW('Employee Master Sheet'!$A$3)+1),ROWS(A$5:A5)),1),"")

                  Comment


                  • #10
                    Re: How to use VLOOKUP when the same value is repeated....

                    Hello StephenR:

                    It works for the first name (cell A5) but then the following cells (A6, A7, etc) have a #NUM! error.

                    Joshua

                    Comment


                    • #11
                      Re: How to use VLOOKUP when the same value is repeated....

                      Remember to use ctrl+shift+enter. For ID, change the Index range from B to C.

                      Comment


                      • #12
                        Re: How to use VLOOKUP when the same value is repeated....

                        Right click on the sheet tab, and click 'view code'

                        Copy the following code in the screen you get. Works for me.

                        Code:
                        Private Sub Worksheet_Change(ByVal Target As Range)
                        Dim a
                        If Target.Address = "$B$2" Then
                        a = Sheets(1).Range("A65536").End(xlUp).Row
                        If a < 6 Then a = 6
                        Sheets(1).Range("A5:I" & a).ClearContents
                        For Each cell In Sheets(2).Range("A3:A" & Sheets(2).Range("A65536").End(xlUp).Row)
                        If cell.Value = Target.Value Then Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).Value = Sheets(2).Range(cell.Address).Offset(0, 1).Value
                        If cell.Value = Target.Value Then Sheets(1).Range("C65536").End(xlUp).Offset(1, 0).Value = Sheets(2).Range(cell.Address).Offset(0, 2).Value
                        Next cell
                        End If
                        End Sub
                        I hope that helps

                        Comment


                        • #13
                          Re: How to use VLOOKUP when the same value is repeated....

                          Hello JasperD:

                          It works! Thank you very much.

                          However, that was only a sample workbook. Would you be able to do the same if I uploaded the actual workbook?

                          I realized I do not need the employee ID returned - only the employee name.

                          Having some trouble attaching the workbook. Let me try again.

                          Thanks,

                          Joshua

                          Comment


                          • #14
                            Re: How to use VLOOKUP when the same value is repeated....

                            I gave you a formula that works.

                            Comment


                            • #15


                              Re: How to use VLOOKUP when the same value is repeated....

                              Originally posted by joshuaprashad01 View Post
                              Hello JasperD:

                              It works! Thank you very much.

                              However, that was only a sample workbook. Would you be able to do the same if I uploaded the actual workbook?

                              I realized I do not need the employee ID returned - only the employee name.

                              Having some trouble attaching the workbook. Let me try again.

                              Thanks,

                              Joshua
                              hi Joshua,

                              sure no problem - send me a pm if necessary.
                              btw Stephen : you're right, but if users mess up the formulas or something in the master sheet gets changed around, it might not work anymore.
                              IMHO using code is the way to go here

                              Comment

                              Working...
                              X