+ Reply to Thread
Results 1 to 11 of 11

Vlookup/Array formula to show multiple results

  1. #1
    Registered User
    Join Date
    09-21-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    28

    Vlookup/Array formula to show multiple results

    Hi All,

    I 'am trying to do a lookup that will return all the results based on the matching criteria, such that,(this is coming from the workbook i have attached) when user selects a company, and a product from that company it should return the product code. I can do this!

    The problem I'm having is that i have the company who have products, but have different product codes for their products.(Please see the workbook, it will make more sense) I want to be able to produce all the product codes for the entered company and chosen product.

    I been looking on-line, and i think i need to do a array for this, so while i try do it myself, any help from anyone would be much appreciated.

    Thank You
    Attached Files Attached Files
    Last edited by Booms; 09-23-2011 at 09:57 AM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Vlookup/Array formula to show multiple results

    Hi,

    You need to use Data Filter Advanced on the Product codes table A1:D8

    On the Main Page make sure your company and product selection have the correct field names above them, i.e. "Company" & "Product". These four cells A1:B2 will be your criteria range.

    Enter the field name "Code" in say A9 on the Main Page and specify this as the output range.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    09-21-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Vlookup/Array formula to show multiple results

    Hey Richard

    OK do i did as you said, but it gives me an error that says "You can only copy filtered data to active sheet"

    Do i need to filter the data??. also i have like 500+ data on my sheets will this still work with so many data??

    Thanks

  4. #4
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Vlookup/Array formula to show multiple results

    hi Booms
    attatchment my help
    Attached Files Attached Files
    Azam
    If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
    For prompt answer, be descriptive, concise, short, direct, and to-the-point.

  5. #5
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Vlookup/Array formula to show multiple results

    there was an error in the previous attatchment.

    use the attatchment with this post
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-21-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Vlookup/Array formula to show multiple results

    Hey Azam,

    Yh thats what i want for it to do. but i cant seem to see, how/what u did?? where do you put the formula?

    Thanks

  7. #7
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Vlookup/Array formula to show multiple results

    Hi,

    I'm a 2000 excel user, so I cannot use some formula I would like to test


    Anyway you could try

    =IF(ISERROR(INDEX('Product Codes'!D$2:D$100;SMALL(IF('Product Codes'!$B$2:$B$100=$B$2;IF('Product Codes'!$C$2:$C$100=$E$2;ROW($A$2:$A$100)-1;""));ROWS('Product Codes'!$A$2:A2))));"";INDEX('Product Codes'!D$2:D$100;SMALL(IF('Product Codes'!$B$2:$B$100=$B$2;IF('Product Codes'!$C$2:$C$100=$E$2;ROW($A$2:$A$100)-1;""));ROWS('Product Codes'!$A$2:A2))))
    The formula has to be confirmed with control+shift + enter and to be copied down

    See the example attached.

    Regards
    Attached Files Attached Files
    Last edited by canapone; 09-23-2011 at 08:49 AM.

  8. #8
    Registered User
    Join Date
    09-21-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Vlookup/Array formula to show multiple results

    Hey Canapone,

    yh that seems to be working great, just what i was looking for. i just have one questions

    Because i have 500+ rows of data, will i be changing wherever you have put 100 to 500, and remove the $ sign from the second part of the range because its very likely the data will be increased.

    Thanks for your help, that's exactly what i have been looking for.

  9. #9
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Vlookup/Array formula to show multiple results

    Ciao, just edit replace 100 to 500 trying to get along with this very touchy array formula

    =IF(ISERROR(INDEX('Product Codes'!D$2:D$500;SMALL(IF('Product Codes'!$B$2:$B$500=$B$2;IF('Product Codes'!$C$2:$C$500=$E$2;ROW($A$2:$A$500)-1;""));ROWS('Product Codes'!$A$2:A2))));"";INDEX('Product Codes'!D$2:D$500;SMALL(IF('Product Codes'!$B$2:$B$500=$B$2;IF('Product Codes'!$C$2:$C$500=$E$2;ROW($A$2:$A$500)-1;""));ROWS('Product Codes'!$A$2:A2))))

    Confirm with control+shift+enter

    The formula for big ranges is quite challenging for the processor.

    In the case you are describing using a pivot table is the most convenient solution.

    Regards

  10. #10
    Registered User
    Join Date
    09-21-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Vlookup/Array formula to show multiple results

    Once again Thanks Canapone

    I'll look into Pivot tables as well.

  11. #11
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Vlookup/Array formula to show multiple results

    formula is given in yellow highligted cells

    since you are using Excel 2007, you can use iferror function to make your formula short
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1