+ Reply to Thread
Results 1 to 3 of 3

Help needed : Multiple condition lookup

  1. #1
    Registered User
    Join Date
    06-20-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    2

    Help needed : Multiple condition lookup

    Hi,

    With reference to the below screenshots;

    this is the core data sheet, whereby a 10 minute time stamp goes across the top, the line and corresponding status down the left side, and the intersection (i.e. where a value is populated), indicates the status of the line for the amount of time shown.

    2018-06-20 14_56_21-OEE input sheet1 Amended.xlsx - Excel.png

    I wish to convert the data shown above into a format such as this shown below, this has to be done via formula as I strictly cannot use any other means. what I am looking to populate is the status in the tall/slim table, from the short/fat one showing above.
    2018-06-20 14_56_57-OEE input sheet1 Amended.xlsx - Excel.png

    In essence then, and if an example is worked, the value in cell F2 would be the rest of a lookup on both line AB1 and time 08:00:00 into the core data table, and the corresponding result would be "machine set-up" from column D - as this is populated with "10" in cell E8. Moving to 08:30 (as 8am-8.20 inclusive is all machine set up), the result I would expect here would be "Line Operating". You will notice some columns are double populated - i.e. 08:40 has values populated against Line operating and Label Change - if its easy and perhaps one way of achieving this would be to select the first non-blank in the range - have this select the first non-blank - thus 08:40 would be "line operating".

    Many thanks,

  2. #2
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    Microsoft 365
    Posts
    152

    Re: Help needed : Multiple condition lookup

    Can you upload a sample workbook? I'm trying to get my head around what you are asking.

  3. #3
    Registered User
    Join Date
    06-20-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    2

    Re: Help needed : Multiple condition lookup

    ok, so will see two tabs within the workbook Minutes and Minutes formatted.

    Minutes is the primary sheet and the Minutes formatted tab is the work in progress where the formula will be applied.

    You will notice that in minutes formatted we have Line and Time as attributes on which the lookup/match should be performed. Thus, what I need is a formula which will search the relevant column (based on the timestamp), and also ensure a match to the line is performed, to find the relevant row where the status is populated
    hence in the example I gave in the original post;

    the result of the first formula in Minutes Formatted - i.e the value populated in cell F2 would be "Machine Set up", as - per the "Minutes" sheet, this is the row populated for Line AB 1, for time 08:00.

    Please let me know if you need any further clarification.
    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. Multiple Condition Lookup and Value Return
    By crawdaddy0623 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2015, 02:31 PM
  2. [SOLVED] Multiple Condition IF Formula - Help Needed!
    By musicman1985 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2014, 02:23 AM
  3. Multiple condition lookup and chart
    By Karthick.icwa in forum Excel General
    Replies: 0
    Last Post: 06-14-2012, 05:04 AM
  4. multiple condition lookup
    By bigbavarian in forum Excel General
    Replies: 5
    Last Post: 08-25-2011, 10:30 PM
  5. [SOLVED] Multiple Condition Help needed....
    By Curtis in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-15-2006, 05:09 PM
  6. Multiple condition help needed
    By Curtis in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-15-2006, 09:41 PM

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