+ Reply to Thread
Results 1 to 5 of 5

Vlookup based on lookup value and multi number criteria

  1. #1
    Registered User
    Join Date
    01-03-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Vlookup based on lookup value and multi number criteria

    I have been stuck with this problem for a while. What I am trying to do is return a reason based on an Order. How it works is that an Order is placed and may have multiple line items which would have a total number of units but when the Order is actually selected, it is done so randomly but shows which Order number was chosen. For example, in Sheet_2, Order X has 2 line items so the total number of units ordered is 9. The first 3 have a reason of "Fix" and the next 6 have a reason of "Clean". Then in Sheet_1, I am trying to return the Reason from Sheet_2 but the problem I am having is that Sheet_1 contains the specific selection number which I cannot figure out a way to work around. Any help is greatly appreciated. Thanks!





    Sheet_1: table with lookup value (item) and want to return value to
    Order, Selection_Date, Selection_#, Returned_Reason
    X 9/5 1 Fix
    X 9/7 5 Clean
    X 9/5 3 Fix
    X 9/7 8 Clean
    Y 9/10 2 Clean
    Y 9/11 5 Upgrade



    Sheet_2: table for lookup array
    Order, Request_Date, Total_#_Requested, Reason
    X 9/4 3 Fix
    X 9/5 6 Clean
    Y 9/7 3 Clean
    Y 9/8 2 Upgrade

  2. #2
    Registered User
    Join Date
    02-21-2014
    Location
    Connecticut, U.S.A.
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Vlookup based on lookup value and multi number criteria

    So I better understand this, Sheet2 has two reasons (fix &clean) for order x. If you want to return a reason back to sheet1, you would need to return two of them, correct?

    What do you mean that in sheet1, it contains the selection number?

    How are you setting up sheet1 to account for a return of more than one reason found in sheet2?

  3. #3
    Registered User
    Join Date
    01-03-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Vlookup based on lookup value and multi number criteria

    So essentially Sheet_1 contains the order and can have multiple lines for the same order but with different amounts requested each time. Sheet_2 specifies which unit was selected. So for example, Oder X is placed twice in Sheet_2 with different number of units requested for each order (3 and 6) but Sheet_2 shows which order has been selected based on a summed up number of units for Order X in Sheet_1. So Order X has a total of 9 units from Sheet_1 but the only values shown are 3 and 6, while Sheet_1 shows specifically the unit ordered, ie, 5 or 8 or whatever. So the first 3 orders (1,2,and 3) should return "Fix" while the remaining (4 through 9) should be "Clean". Does this help?

    Sheet1.JPGSheet2.JPG

  4. #4
    Registered User
    Join Date
    02-21-2014
    Location
    Connecticut, U.S.A.
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Vlookup based on lookup value and multi number criteria

    I'm trying to follow what you've described but it's still not clear to me.

    This is how I am reading your tables. Regarding sheet1, I understand it to mean: on 9/5 an order was placed for X two times. One order for X was made with Selection#1 with a returned reason of Fix. Another order for X was made with Selection#3 with a returned reason for Fix. That's two orders for X on 9/5 with two different selection #s, both with Fix. So for 9/5, I compare that to the sheet2 table but I don't see a corresponding date and return reason for fix. I only see 9/4 on sheet two and that's for clean.

    I guess I'm still struggling to understand exactly how you are trying to do this vlookup. Do the dates even matter in this case?

  5. #5
    Registered User
    Join Date
    01-03-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Vlookup based on lookup value and multi number criteria

    So the dates do not matter I guess. I mean I'm not sure if a vlookup is the best way to go about solving this but that was my initial thought. Essentially the table array in Sheet_2 does not have the actual Selection# from Sheet_1, otherwise this would be a simple solution. It contains the amount ordered at each time and then Sheet_1 has the number selected based on the total of the orders but which have different reasons. Does that help?

+ 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. [SOLVED] How do I do multi VLOOKUP's based on certain criteria per cell?
    By bj in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-06-2005, 10:05 AM
  2. How do I do multi VLOOKUP's based on certain criteria per cell?
    By bj in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 06:05 AM
  3. How do I do multi VLOOKUP's based on certain criteria per cell?
    By Milky_UK in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  4. [SOLVED] How do I do multi VLOOKUP's based on certain criteria per cell?
    By Milky_UK in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  5. How do I do multi VLOOKUP's based on certain criteria per cell?
    By Milky_UK in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2005, 01:05 PM

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