Results 1 to 7 of 7

lookup an column and locate the first value greater than 0 and return the hh:s

Threaded View

  1. #1
    Registered User
    Join Date
    05-19-2022
    Location
    KL, Malaysia
    MS-Off Ver
    Microsoft 365 MSO (Version 2204 Build 16.0.15128.20240) 64-bit
    Posts
    3

    lookup an column and locate the first value greater than 0 and return the hh:s

    Hi guys, I have troubles finding correct formulas in excel and I hope that someone can assist me here.
    What I am trying to do is to lookup an column and locate the first value greater than 0 and return the hh:ss located in the same row but on different column.
    Here is the idea:

    A B C D E
    1 01/01/2022 11:55:00 AM 0 0 0 0
    2 01/01/2022 12:00:00 PM 0 1 1 0
    3 01/01/2022 12:05:00 PM 1 2 2 0
    4 01/01/2022 12:10:00 PM 2 3 3 1

    Return should be:
    - column B = 12:05
    - column C = 11:55
    - column D = 11:55
    - column E = 12:10

    If there is no value greater than 0 in the column, the return should be "OFF"

    I was trying =INDEX($A2:$A289;MATCH(B2;B2:B289;1))+B293 -> but the formula is not working all the time (refer to attachment in CELL D888). Also I have to add up 00:05 hours to the formula to get the correct result. I was not able to implement OFF condition.

    Further, I am looking for a same formula to locate the last value greater than 0 in the same column, and return similar as with above.

    A B C D E
    1 01/01/2022 11:55:00 AM 1 1 1 1
    2 01/01/2022 12:00:00 PM 2 0 2 2
    3 01/01/2022 12:05:00 PM 0 0 3 3
    4 01/01/2022 12:10:00 PM 0 0 0 0

    Return should be:
    - column B = 12:00
    - column C = 11:55
    - column D = 12:05
    - column E = 12:05

    If there is no value greater than 0 in the column, the return should be "OFF"

    For this I was trying =IFERROR((LOOKUP(2;1/(B2:B289>B3);$A2:$A289));"OFF") -> I believe that this formula works, but please review it.

    __

    From time to time I have missing data (see column K), how this can be fixed in formula to take the first value?

    Thank in advance!

    Regards
    Attached Files Attached Files
    Last edited by AliGW; 05-19-2022 at 04:06 AM. Reason: Title changed - please think more carefully about your thread titles in future!

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Index match not working if lookup column contains formulas?
    By Filos in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-15-2021, 04:49 PM
  2. Replies: 5
    Last Post: 10-20-2020, 01:23 PM
  3. [SOLVED] Lookup or Index Formulas
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-07-2015, 07:54 AM
  4. Help with Lookup / Index / Match formulas
    By DanWaite in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-12-2015, 03:34 PM
  5. Replies: 1
    Last Post: 03-21-2015, 07:46 PM
  6. Replies: 7
    Last Post: 06-19-2011, 12:51 PM
  7. [SOLVED] Index/ Lookup formulas and fuzzy matching
    By JaB in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-10-2005, 06:10 AM

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