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
sanjangeorgeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

telyni19Commented:
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.
0
sanjangeorgeAuthor Commented:
I see, what would be the simplest VBA approach then?
0
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
0
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

sanjangeorgeAuthor Commented:
its not the way i'd like to display the data.
0
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

0
telyni19Commented:
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).
0
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
0
telyni19Commented:
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
        Do
            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

MultipleProductsExample.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.