+ Reply to Thread
Results 1 to 23 of 23

Lookup 2 or more values to return to cell

  1. #1
    Forum Contributor
    Join Date
    02-12-2018
    Location
    Clearwater, FL
    MS-Off Ver
    MS 365
    Posts
    214

    Lookup 2 or more values to return to cell

    I have a workbook that has a customer's info in it with a data source & I am trying to do the following:

    Worksheet("TSA Request")
    Range F5 = Customer ID #
    Range F7 = I want a drop down list of the container numbers they have

    Worksheet("Customers")
    Range(P:P) = List of Customer ID#'s
    Range(BP:BP) = List of Customer's Containers

    I want to be able to put in cell F5 the Customer's ID # & it will allow me to select from a drop down list in cell F7 of the container #'s for that customer or both containers. Any help would be greatly appreciated! I would upload the workbook but it is too big for even a zip file

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Lookup 2 or more values to return to cell

    Attach a sample workbook (not image). 20-30rows will do.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Contributor
    Join Date
    02-12-2018
    Location
    Clearwater, FL
    MS-Off Ver
    MS 365
    Posts
    214

    Re: Lookup 2 or more values to return to cell

    Here is a sample workbook - basically I want the drop down list to give both containers & the last option would be both containers or how many ever the customer has.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Lookup 2 or more values to return to cell

    See attached:


    Sheet1 has named range "Containers"

    in A2

    =IFERROR(INDEX(Customers!$BP$2:$BP$100,SMALL(IF(Customers!$AP$2:$AP$100='TSA Request'!$F$6,ROW(Customers!$BP$2:$BP$100)-ROW($BP$2)+1,""),ROWS($1:1))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Then copy down

    in "TSA Request"

    in F7

    Data Vlalidation

    Allow: List

    Source: =Containers
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    02-12-2018
    Location
    Clearwater, FL
    MS-Off Ver
    MS 365
    Posts
    214

    Re: Lookup 2 or more values to return to cell

    What would I need to change if I want the Order Numbers to do the same thing?

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Lookup 2 or more values to return to cell

    The code does it on ORDER NUMBER


    =IFERROR(INDEX(Customers!$BP$2:$BP$100,SMALL(IF(Customers!$AP$2:$AP$100='TSA Request'!$F$6,ROW(Customers!$BP$2:$BP$100)-ROW($BP$2)+1,""),ROWS($1:1))),"")

  7. #7
    Forum Contributor
    Join Date
    02-12-2018
    Location
    Clearwater, FL
    MS-Off Ver
    MS 365
    Posts
    214

    Re: Lookup 2 or more values to return to cell

    I think we missed a mark somewhere - based on the Account number I want it to list the Order #'s and then based off the order number selected in "TSA Request".Range(F6) I want to be able to select the container number in that order number? I used the formulas you provided and made sure they were array formulas and it is not populating! Can you help please?

  8. #8
    Forum Contributor
    Join Date
    02-12-2018
    Location
    Clearwater, FL
    MS-Off Ver
    MS 365
    Posts
    214

    Re: Lookup 2 or more values to return to cell

    I do want to inform you that my list on Worksheets("Customers") is over 200K entries. VERY long list!

  9. #9
    Forum Contributor
    Join Date
    02-12-2018
    Location
    Clearwater, FL
    MS-Off Ver
    MS 365
    Posts
    214

    Re: Lookup 2 or more values to return to cell

    To clarify, when I put in the customer number in cell F5 on "TSA Request", I want cell F6 to be a drop down of the order numbers for that customer. Then based off which order number I select in Cell (F6) I want to select the container number that corresponds with the order number!

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Lookup 2 or more values to return to cell

    Try:

    in "Sheet1"

    in A2

    Please Login or Register  to view this content.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    in B2 (List of UNIQUE orders)

    Please Login or Register  to view this content.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    in C2

    Please Login or Register  to view this content.
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.


    " named ranges : "Containers" and "Orders"

    in "TSA Request"

    in F6

    DV

    List

    =Orders

    in F7

    DV

    list

    =Containers

    I recommend you look here and understand why your data needs to be reformatted in "normalized" form.

    https://support.microsoft.com/en-gb/...ization-basics

    For example: if a customer address changes, you have to change many records in your current file. If there was a separate address table, linked by Customer number, then you only change one entry in the "customer" table..
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    02-12-2018
    Location
    Clearwater, FL
    MS-Off Ver
    MS 365
    Posts
    214

    Re: Lookup 2 or more values to return to cell

    OK I need your help again! After speaking with my report builder - we got the "Customers" tab cleaned up with the only data I need. If you look on "TSA Request" you will see in Column T:U has Containers & Orders as a heading. I would like for the container #'s & Order #'s populate here and I will be able to make the list as a drop down! Is there a way that I would be able to have the list show all the container numbers along with any combination of how those containers could be listed? (Example: If I have container #4561BX & #35B8X & 175VX4 - I would like the list to display the following:
    4561BX
    35B8X
    175VX4
    All containers)

    I have attached the new workbook for your reference - with the new report build. Thank you for all of your help! You have saved me a lot of stress! I tried moving Sheet1 from your example over to this workbook but it doesn't work the same.
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Lookup 2 or more values to return to cell

    I would be able to have the list show all the container numbers along with any combination of how those containers could be listed?
    I don't understand the above.

    and "TSA requests" appears to be protected as I cannot enter anything in columns T and U.

    And don't you want Container Drop down just to pertain to a selected order?

  13. #13
    Forum Contributor
    Join Date
    02-12-2018
    Location
    Clearwater, FL
    MS-Off Ver
    MS 365
    Posts
    214
    Quote Originally Posted by JohnTopley View Post
    I don't understand the above.

    and "TSA requests" appears to be protected as I cannot enter anything in columns T and U.

    And don't you want Container Drop down just to pertain to a selected order?
    Sorry the password is "Morgan1968" i have a macro that locks it each time you reset the form!

  14. #14
    Forum Contributor
    Join Date
    02-12-2018
    Location
    Clearwater, FL
    MS-Off Ver
    MS 365
    Posts
    214
    Quote Originally Posted by JohnTopley View Post
    I don't understand the above.

    and "TSA requests" appears to be protected as I cannot enter anything in columns T and U.

    And don't you want Container Drop down just to pertain to a selected order?
    Correct the container numbers to pretain to the order number! So basically with the container numbers i would like for it to list All Contianers at the end of the numbers so if it is say all 3 containers they can select all of them instead of sending one by one

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Lookup 2 or more values to return to cell

    See attached:

    5 named ranges:

    Containers: list of all containers (not sure why this is needed)

    in T2

    =IFERROR(INDEX(Customers!$I$2:$I$1000,SMALL(IF(Customers!$I$2:$I$1000<>"",ROW(Customers!$I$2:$I$1000)-ROW($I$2)+1,""),ROWS($I$2:I2))),"")

    Orders: list of orders

    in V2

    =IFERROR(INDEX(Customers!$J$2:$J$1000,SMALL(IF(Customers!$J$2:$J$1000<>"",ROW(Customers!$J$2:$J$1000)-ROW($J$2)+1,""),ROWS($J$2:$J2))),"")

    Containers_per_order: containers for a given order

    in V3

    =IFERROR(INDEX(Customers!$I$2:$I$1000,SMALL(IF(Customers!$J$2:$J$1000=$F$6,ROW(Customers!$I$2:$I$1000)-ROW($I$2)+1,""),ROWS($I$2:$J2))),"")

    All the above ..

    Array-entered (CSE)

    Number_of_ Containers: V3

    No_Containers: use to display message if no containers for a selected order

    in F6 (Data Validation)

    Allow: List
    Source: =Orders

    in F7 (data validation)

    Allow: List
    Source: =IF(Number_of_Containers=0,No_Containers,Containers_per_Order)
    Attached Files Attached Files
    Last edited by JohnTopley; 04-30-2018 at 04:19 AM.

  16. #16
    Forum Contributor
    Join Date
    02-12-2018
    Location
    Clearwater, FL
    MS-Off Ver
    MS 365
    Posts
    214

    Re: Lookup 2 or more values to return to cell

    The only issue here is that when I type in the Customer ID number I want it only to show the orders related to the Customer Number & based off that order number, only show the containers that is assigned to that order.

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Lookup 2 or more values to return to cell

    See attached with named range "Orders_per_CUSTOMER"

    DV changed to reference the above
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    02-12-2018
    Location
    Clearwater, FL
    MS-Off Ver
    MS 365
    Posts
    214

    Re: Lookup 2 or more values to return to cell

    This is perfect - thank you so much!!! Very big help!

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Lookup 2 or more values to return to cell

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  20. #20
    Forum Contributor
    Join Date
    02-12-2018
    Location
    Clearwater, FL
    MS-Off Ver
    MS 365
    Posts
    214

    Re: Lookup 2 or more values to return to cell

    One last question is in the order number (F6) is there away we can have it say no orders if it can not find the order pertaining to that CID?

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Lookup 2 or more values to return to cell

    Look at the solution for "No containers": have a go at a similar solution for "No Orders".

  22. #22
    Forum Contributor
    Join Date
    02-12-2018
    Location
    Clearwater, FL
    MS-Off Ver
    MS 365
    Posts
    214

    Re: Lookup 2 or more values to return to cell

    How about removing duplicates in the order_per_Customer? I use Customer number :132716759 & it shows order #1078419 twice - any ideas?

  23. #23
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Lookup 2 or more values to return to cell

    In Y2 (named range "Unique_Orders")

    =IFERROR(LOOKUP(2,1/(COUNTIF($Y$1:Y1,Orders_per_Customer)=0),Orders_per_Customer),"")

    copy down

    in F6 (DV)

    source: =Unique_Orders
    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)

Similar Threads

  1. Replies: 3
    Last Post: 03-10-2017, 07:56 PM
  2. return multiple values for one lookup value in the same cell
    By TMC2016 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-15-2016, 06:17 AM
  3. [SOLVED] Return Multiple Lookup Values to One Cell
    By excelnoob927 in forum Excel General
    Replies: 3
    Last Post: 12-23-2014, 03:38 PM
  4. Lookup to return multiple cell values
    By gee83 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-11-2014, 09:20 AM
  5. Replies: 4
    Last Post: 07-07-2006, 08:55 AM
  6. Using a lookup to return multiple values in one cell??
    By zim_zimmer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-07-2006, 08:10 AM
  7. [SOLVED] Using a lookup to return multiple values in one cell??
    By zim_zimmer in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-07-2006, 05:35 AM

Tags for this Thread

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