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" <MRT@discussions.microsoft.com> wrote in message
news:7DC05C1F-A037-4216-AC45-CE4A5990D1EA@microsoft.com...
>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

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" <MRT@discussions.microsoft.com> wrote in message
> news:7DC05C1F-A037-4216-AC45-CE4A5990D1EA@microsoft.com...
> >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
>
>
>

>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" <MRT@discussions.microsoft.com> wrote in message
news:0C99B94B-F546-4EEA-A3D5-58BB118F2EF8@microsoft.com...
>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" <MRT@discussions.microsoft.com> wrote in message
>> news:7DC05C1F-A037-4216-AC45-CE4A5990D1EA@microsoft.com...
>> >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
>>
>>
>>

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

Try this:

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

Format the cell as DATE.

Biff

"MRT" <MRT@discussions.microsoft.com> wrote in message
news:EDAFA136-EB83-4ACE-8C2D-C31DE8109FB3@microsoft.com...
> 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
>
>
>