Simple way to return multiple look up values?

The work flow:

The user selects the company using a drop down and i want to display a list of products made by that company.
- i vlookup the company index based on the company thats selected
- i have a value lets say "2" in a cell
- i want to search the sheet below and return all products with the company index of 2.

The sheet follows the pattern below with 100's of rows. A simple formula is what i'm looking for.

Company Index | Product Index | Company Name | Product Name | Sales data
Who is Participating?
telyni19Connect With a Mentor Commented:
Please see the attached file and code. The drop-down is controlled through Data Validation using a named range for the company list. With the named range, the company list could even be on a separate sheet to keep it out of the way. I've got it all on the same page so you can see what the relationships are.

The Worksheet_Change event tests to see whether the changed cell is the drop-down cell; if not, it does nothing, but if so, it runs a script to find all products that match the company index. Cell H2 has a hidden formula (text in white) to use VLookup to return the index from the selected name. Cell H1 has a hidden formula to count the company names in the list in case you wanted to set up some sort of dynamic reference for the company list or do something else with the number of companies.

Instead of hard-coding the references to these cells with useful values, as well as which cell is the drop-down and which cell is the place to put the concatenated result, I made each of those cells a named range also and used the names in the code. If you want to rearrange the layout, no problem, just make sure the names point to the right cells and the code won't need to change.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCIndex As Integer
Dim intRow As Integer
Dim strValues As String
Dim rngVal As Range
Dim strAddress As String

If Target.Address = ActiveSheet.Range("CompanyChoice").Address Then
    strValues = ""
    intCIndex = ActiveSheet.Range("SelIndex").Value
    Set rngVal = ActiveSheet.Range("A:A").Find(What:=intCIndex, LookIn:=xlValues, After:=ActiveSheet.Range("A1"), LookAt:=xlWhole, SearchOrder:=xlByColumns)
    If Not rngVal Is Nothing Then
        strAddress = rngVal.Address
            If Not strValues = "" Then strValues = strValues & ", "
            strValues = strValues & rngVal.Offset(0, 3).Value
            Set rngVal = ActiveSheet.Range("A:A").FindNext(rngVal)
        Loop While Not rngVal Is Nothing And rngVal.Address <> strAddress
    End If
    ActiveSheet.Range("ProductList").Value = strValues
End If
End Sub

Open in new window

A simple formula in a cell won't return multiple rows of data. Are you trying to return a concatenated list in a string variable or cell value, or are you trying to show all the rows that match the company index? If the latter, you could use a macro to trigger an autofilter on the page to filter by company index, but a cell formula can't do that. If the former, you'll still need a macro since there's no good way to run a custom loop on a set of cells with one formula.
sanjangeorgeAuthor Commented:
I see, what would be the simplest VBA approach then?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Arno KosterCommented:
If you only want to display these products, why not use autofiltering ?

when you have a setup as above, select the column headers and activate filtering.
When a user then filters on a single company, or a combination of companies, only those will be visible.

in this case it is not necessary to use  formula's
sanjangeorgeAuthor Commented:
its not the way i'd like to display the data.
Arno KosterCommented:
or in a macro it would look like
Sub filter(index)
'-- assuming A column for company index
'--          B column for product index
'--          C column for company name
'--          D column for product name
'--          E column for sales data

    For Each Row In UsedRange.Rows
        If Row.Cells(1) = index Then
            '[..] do something with the data, eg.
            Debug.Print "Company " & Row.Cells(3) & " sells product " & Row.Cells(4) & " for " & Row.Cells(5)
        End If
    Next Row

End Sub

Open in new window

You didn't really answer the question of which approach you wanted, except in reply to akoster where you said you didn't want to display the data using filtering, so I'm guessing you want to concatenate the information?

So you have a drop-down for the company choice. The technique I suggest is that when the value of the drop-down changes, the code determines which company index to use, searches the data for all produce names matching the company index, and concatenates them into a text string to either display on the page somewhere or show to the user.

I will be happy to construct a sample file that does this and attach it here for you. If you have an example file with a few rows of data that you could share, that would be better, as I could add on to you existing structure and layout rather than making my own. In particular, if you already have a drop-down set up, you'll want the code to respond to that drop-down, so it will matter how you have the drop-down set up (Excel object, data validation, etc).
sanjangeorgeAuthor Commented:
telyni19: could you set this file up and show me what you think the best practices would be.

akoster: thanks for this i'll have a look at it now
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.