+ Reply to Thread
Results 1 to 20 of 20

lookup based on two conditions

  1. #1
    Registered User
    Join Date
    07-10-2009
    Location
    Hudson, New Hampshire USA
    MS-Off Ver
    Excel 2003
    Posts
    37

    lookup based on two conditions

    I know that there are bunches of threads concerning lookups with multiple criteria, but I just can't figure out how to translate one to my situation. I want to return a value based on an item name which is in column A, and an operation which is in row 1.

    The array from which I need to look up the value contains part number in column B, operation in column G, and the actual value I need returned in column H.


    So I need to return something like this:

    __________Operation 1

    PartA-----------5


    From this:

    ...Part A...Operation 1...5

    Thank you for the help and please let me know if this is unclear.
    Last edited by ndenaro; 07-14-2009 at 11:58 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: lookup based on two conditions

    Use Index/Match

    e.g

    =Index($A$1:$D$100,Match(G2,$A$1:$A$100,0),Match(F2,$A$1:$D$1,0))

    where A1:D100 contains your entire table and G2 and F2 contain the values to look up in column A and Row 1, respectively.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    07-10-2009
    Location
    Hudson, New Hampshire USA
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: lookup based on two conditions

    I am still not getting it to return a value, I am getting a N/A error even when the value is in the table. The error is probably on my half, and I don't doubt your formula will work, but I have included an example of what the workbook looks like to try to make it more clear. Sheet one shows the Part names and operations in columns and rows respectively, and what I have left as "x" are the time values that I am trying to lookup and return. Sheet 2 shows the table from which I am trying to look up the time values.
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: lookup based on two conditions

    I don't get your sample... why the x's.. should the times that are in column C of sheet 2 be where the x's are? And is column C of sheet 2 what you are actually trying to return based on column A and B?

    Is this sample really representative of your actual?

  5. #5
    Registered User
    Join Date
    07-10-2009
    Location
    Hudson, New Hampshire USA
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: lookup based on two conditions

    Sorry, I don't know what I was doing with the x's, but you are correct that is where I want the times from column c of sheet2 to go, I have replaced them in the attachment. This sample is representative of what I want to happen but much simpler than the tables I have. The actual sheets contain much more information (though not important to me). If this sample is not helpful to you I can try to come up with a more representative sample but it may take me a minute or two. I will need to make significant changes to the original before I could post it as a sample, but please let me know if you think it would be helpful. I really appreciate you helping me out with this.
    Attached Files Attached Files

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: lookup based on two conditions

    In your actual sheets are the items in column B of Sheet2 exactly matching Row 1 of Sheet1? They ideally should match for you to get results...

    I changed your column B in the attached to show Operation1 instead of Op1, etc.. and the formulas now work.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-10-2009
    Location
    Hudson, New Hampshire USA
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: lookup based on two conditions

    Attatched is a much more accurate version of the worksheets. Sheet 2 is what I have, sheet 1 is what I am trying to fill in with time values. Thanks again.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-10-2009
    Location
    Hudson, New Hampshire USA
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: lookup based on two conditions

    To answer your question as best as I understand it, no they do not match up. Sheet 2 is a master list of all parts and their operation times. Sheet one is only parts that have orders made against them. So all parts in sheet one can be found somewhere in sheet two, but not all parts in sheet 2 are contained in sheet 1.
    Last edited by ndenaro; 07-13-2009 at 10:22 AM.

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: lookup based on two conditions

    See attached...

    Formula used in H2:

    =INDEX(Sheet1!$A$1:$L$4,MATCH(B2,Sheet1!$A$1:$A$4,0),MATCH(F2,Sheet1!$A$1:$L$1,0))

    copied down.

    Note: You currently have your workbook set for R1C1 referencing. To go back to the A1 style... go to Tools|Options and uncheck R1C1 reference style from the General tab.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-10-2009
    Location
    Hudson, New Hampshire USA
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: lookup based on two conditions

    ok, I will try to work with what you have, but this is sort of backwards from what I need. I already have the times entered in sheet 2 so I am trying to lookup and fill them in sheet 1. Correct me if I am wrong, but I think in your example they are being filled into sheet 2 from sheet 1.

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: lookup based on two conditions

    Ok.. I misunderstood then... hold a few moments...

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: lookup based on two conditions

    Ok see attached...

    Formula in B2 of Sheet1:

    =SUMPRODUCT(--(Sheet2!$B$2:$B$24=$A2),--(Sheet2!$F$2:$F$24=B$1),Sheet2!$H$2:$H$24)

    copied across the table horizontally and vertically.

    If you don't want 0's to show, you can go to Tools|Options and remove the Zero Values checkbox from the View tab.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    07-10-2009
    Location
    Hudson, New Hampshire USA
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: lookup based on two conditions

    Success! Thank you so much.

  14. #14
    Registered User
    Join Date
    07-10-2009
    Location
    Hudson, New Hampshire USA
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: lookup based on two conditions

    hmm...althought your formula does work excellently, once copied over the number of cells required, my workbook slows down to intolerable speeds. Is there any possible way to do this in a simpler fashion? If not, I still thank you for all of your time and help.

  15. #15
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: lookup based on two conditions

    Try a Pivot Table... see Sheet 4 attached.

    While in Sheet2, go to Data|Pivot Table...

    Click Next and make sure the entire table is covered in the suggested range..

    Click Next, Click Layout

    Drag Part Name to the Row area,
    Drag Operation to Column area,
    Drag Time to the Data area (and make sure it says Sum of Time. If not, double-click and select Sum).

    Click Ok.. then click Next.

    choose your destination and click Finish.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    07-10-2009
    Location
    Hudson, New Hampshire USA
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: lookup based on two conditions

    the only problem I forsee with this strategy is that sheet 2 is a master list of parts and their operation times, whereas I only want to view those with current orders (those listed in sheet 1). The reason is that I have more to do after this list is generated.

  17. #17
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: lookup based on two conditions

    I think that's way I suggested the formula approach the first time....

    .. can you perhaps add a column to Sheet1, that shows Current/Not Current and then include that column in the Row area of the Pivot Table, and then filter for the Pivot Table to show the Current only data?

  18. #18
    Registered User
    Join Date
    07-10-2009
    Location
    Hudson, New Hampshire USA
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: lookup based on two conditions

    worth a try...I could create another column in sheet 2 to not if a part number was active. What would this function look like? Something to say if the value in column B of sheet 2 can be found in column A of sheet1, then it is active. I have played around with a few but can't get anything to work.

  19. #19
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: lookup based on two conditions

    Well the pivot table automatically creates Column A in Sheet1.. so you would be doing some circular referencing....

    So you would have an extra column A... and use the formula in Sheet2..

    =If(isnumber(match(B2,Sheet1!A:A,0)),"Active","Inactive") copied down.

  20. #20
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: lookup based on two conditions

    Sample attached... sheet1
    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