+ Reply to Thread
Results 1 to 6 of 6

need to retrieve dates from lookup

  1. #1
    MRT
    Guest

    need to retrieve dates from lookup

    I am trying to do a lookup formula with if then... =If(hlookup("1C",
    B1:B10,1,False),lookup("1C",B1:B10,1)

    Here is my data:

    3/1/05 3/2/05 3/3/05 3/05/05
    1C 1C

    With this information, I want to be able to find all the cells that have
    "1C" and return with the date that corresponds to it. For example: "1C" is
    in both the dates 3/1/05 and 3/3/05. I want a formula that will retrieve
    those two dates. And if there aren't any "1C", then I want to get a blank
    cell or a N/A

  2. #2
    Biff
    Guest

    Re: need to retrieve dates from lookup

    Where EXACTLY is this data?

    In your formula you use a range of B1:B10 but the sample data doesn't look
    like it follows that layout at all!

    Biff

    "MRT" <[email protected]> wrote in message
    news:[email protected]...
    >I am trying to do a lookup formula with if then... =If(hlookup("1C",
    > B1:B10,1,False),lookup("1C",B1:B10,1)
    >
    > Here is my data:
    >
    > 3/1/05 3/2/05 3/3/05 3/05/05
    > 1C 1C
    >
    > With this information, I want to be able to find all the cells that have
    > "1C" and return with the date that corresponds to it. For example: "1C"
    > is
    > in both the dates 3/1/05 and 3/3/05. I want a formula that will retrieve
    > those two dates. And if there aren't any "1C", then I want to get a blank
    > cell or a N/A




  3. #3
    MRT
    Guest

    Re: need to retrieve dates from lookup

    I have dates from B1:B10 and underneath each date it has either blank cells
    or it has "1C" , "2C", "3C" underneath it. Now I want to retrieve all the
    dates that have "1C" underneath it.

    "Biff" wrote:

    > Where EXACTLY is this data?
    >
    > In your formula you use a range of B1:B10 but the sample data doesn't look
    > like it follows that layout at all!
    >
    > Biff
    >
    > "MRT" <[email protected]> wrote in message
    > news:[email protected]...
    > >I am trying to do a lookup formula with if then... =If(hlookup("1C",
    > > B1:B10,1,False),lookup("1C",B1:B10,1)
    > >
    > > Here is my data:
    > >
    > > 3/1/05 3/2/05 3/3/05 3/05/05
    > > 1C 1C
    > >
    > > With this information, I want to be able to find all the cells that have
    > > "1C" and return with the date that corresponds to it. For example: "1C"
    > > is
    > > in both the dates 3/1/05 and 3/3/05. I want a formula that will retrieve
    > > those two dates. And if there aren't any "1C", then I want to get a blank
    > > cell or a N/A

    >
    >
    >


  4. #4
    Biff
    Guest

    Re: need to retrieve dates from lookup

    >I have dates from B1:B10 and underneath each date it has either blank cells
    >or it has "1C" , "2C", "3C" underneath it. Now I want to retrieve all the
    >dates that have "1C" underneath it.


    Are you sure you're describing this correctly?

    Did you mean you have dates in B1:J1 and in B2:J2 you have either empty
    cells or the codes?

    Biff

    "MRT" <[email protected]> wrote in message
    news:[email protected]...
    >I have dates from B1:B10 and underneath each date it has either blank cells
    > or it has "1C" , "2C", "3C" underneath it. Now I want to retrieve all the
    > dates that have "1C" underneath it.
    >
    > "Biff" wrote:
    >
    >> Where EXACTLY is this data?
    >>
    >> In your formula you use a range of B1:B10 but the sample data doesn't
    >> look
    >> like it follows that layout at all!
    >>
    >> Biff
    >>
    >> "MRT" <[email protected]> wrote in message
    >> news:[email protected]...
    >> >I am trying to do a lookup formula with if then... =If(hlookup("1C",
    >> > B1:B10,1,False),lookup("1C",B1:B10,1)
    >> >
    >> > Here is my data:
    >> >
    >> > 3/1/05 3/2/05 3/3/05 3/05/05
    >> > 1C 1C
    >> >
    >> > With this information, I want to be able to find all the cells that
    >> > have
    >> > "1C" and return with the date that corresponds to it. For example:
    >> > "1C"
    >> > is
    >> > in both the dates 3/1/05 and 3/3/05. I want a formula that will
    >> > retrieve
    >> > those two dates. And if there aren't any "1C", then I want to get a
    >> > blank
    >> > cell or a N/A

    >>
    >>
    >>




  5. #5
    MRT
    Guest

    Re: need to retrieve dates from lookup

    I'm sorry...

    Yes, I have dates 3/1/06 - 3/7/06 in one row B1:H1. And in B2:H2 has "1C",
    "2C", "3C" and blank cells. In cell D6, I want to create a formula that will
    give me the date that corresponds with "1C". For example if "1C" is in C2, I
    want to get the date that is above C2. The cell above C2 is C1 with the date
    3/2/06. So now I want the date 3/2/06 to appear in the designated cell (D6).

    3/1/2006 3/2/2006 3/3/2006 3/4/2006 3/5/2006 3/6/2006 3/7/2006 1C 2C 3C




  6. #6
    Biff
    Guest

    Re: need to retrieve dates from lookup

    Try this:

    =INDEX(B1:H1,MATCH("1C",B2:H2,0))

    Format the cell as DATE.

    Biff

    "MRT" <[email protected]> wrote in message
    news:[email protected]...
    > I'm sorry...
    >
    > Yes, I have dates 3/1/06 - 3/7/06 in one row B1:H1. And in B2:H2 has
    > "1C",
    > "2C", "3C" and blank cells. In cell D6, I want to create a formula that
    > will
    > give me the date that corresponds with "1C". For example if "1C" is in
    > C2, I
    > want to get the date that is above C2. The cell above C2 is C1 with the
    > date
    > 3/2/06. So now I want the date 3/2/06 to appear in the designated cell
    > (D6).
    >
    > 3/1/2006 3/2/2006 3/3/2006 3/4/2006 3/5/2006 3/6/2006 3/7/2006 1C 2C 3C
    >
    >
    >




+ 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