Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

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

  1. #1
    Join Date
    30th August 2010
    Posts
    3

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by AAE; September 6th, 2010 at 21:59. Reason: revise thread title

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd September 2010
    Location
    Mumbai, India
    Posts
    6

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th September 2009
    Posts
    1,599

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    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

  5. #5
    Join Date
    30th August 2010
    Posts
    3

    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 at 22:54. Reason: back to back replies

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Revised Simplex Method to solve LP problem
    By nfung in forum EXCEL HELP
    Replies: 4
    Last Post: April 20th, 2006, 20:32
  2. Replies: 3
    Last Post: July 26th, 2005, 17:06
  3. lookup date problem in excel
    By opeyemi1 in forum EXCEL HELP
    Replies: 10
    Last Post: May 29th, 2004, 02:47
  4. Replies: 4
    Last Post: May 19th, 2003, 17:21
  5. Replies: 1
    Last Post: March 7th, 2003, 19:10

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
  •  
porno