Announcement

Collapse
No announcement yet.

Look up date value and indicate status based on match

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Look up date value and indicate status based on match



    Hi, I need to see if a particular person has taken leave on any given day. I am attaching a file which is abridged to make it small. The actual file is much larger and hence needs a formula/function. Please help.
    Attached Files
    Last edited by AAE; September 6th, 2010, 20:59. Reason: revise thread title

  • #2
    Re: Please help solve this excel lookup problem

    how do you determine the leave ?

    means do you enter "A" for absent or some other text?

    Comment


    • #3
      Re: Please help solve this excel lookup problem

      Try:

      =IF(ISNA(MATCH(A19,B2:BU2,0)),"Invalid Date",IF(INDEX(B3:AU8,MATCH(A17,A3:A8,0),MATCH(A19,B2:BU2,0)),"Yes","No"))

      The Name list is validated so cannot return an #N/A from the row MATCH, but someone could select a date outside the range of your table, so if the column MATCH is #N/A then inform that it is an Invalid Date. To avoid doing the column MATCH twice. Change the main formula to:

      =IF(ISNA(A23),"Invalid Date",IF(INDEX(A2:AU8,MATCH(A17,A2:A8,0),A23),"Yes","No"))

      Then A23 would contain:

      =MATCH(A19,B2:BU2,0)

      A23 can be anywhere on your worksheet.

      Comment


      • #4
        Re: Look up date value and indicate status based on match

        Hello rahul,

        Welcome to Ozgrid.

        Thread titles are important for obtaining good search results and should be written with this thought in mind -- that is, the title should be search friendly, meaning a search using YOUR title as the search terms will yield good results. Thread titles should concisely and accurately describe the thread contents or objective.

        Your thread title of "Please help solve this excel lookup problem" does not adequately describe your thread and is of little help to those searching the forum for solutions to a similar need. As an example for future threads, note the change in your title, which is based on your thread objective and is more specific relative to your need.
        AAE
        ----------------------------------------------------

        Forum Rules | Message to Cross Posters | How to use Tags

        Comment


        • #5
          Look up date value and indicate status based on match

          Works like a charm. Thanks so much :D

          The days marked as 1 are the days they are on leave.
          Last edited by AAE; September 6th, 2010, 21:54. Reason: back to back replies

          Comment


          • #6


            Re: Look up date value and indicate status based on match

            ashbar,

            Please do not quote entire posts. Quote only if it is needed to provide clarity and context for your reply and only quote the specific part of the post that is relevant.
            This will keep the thread clean and uncluttered, making it easier to read.
            AAE
            ----------------------------------------------------

            Forum Rules | Message to Cross Posters | How to use Tags

            Comment

            Working...
            X