software

I am trying to do a lookup formula with if then... =If(hlookup(quot;1Cquot;,
B1:B10,1,False),lookup(quot;1Cquot;,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
quot;1Cquot; and return with the date that corresponds to it. For example: quot;1Cquot; 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 quot;1Cquot;, then I want to get a blank
cell or a N/A

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

quot;MRTquot; gt; wrote in message
...
gt;I am trying to do a lookup formula with if then... =If(hlookup(quot;1Cquot;,
gt; B1:B10,1,False),lookup(quot;1Cquot;,B1:B10,1)
gt;
gt; Here is my data:
gt;
gt; 3/1/05 3/2/05 3/3/05 3/05/05
gt; 1C 1C
gt;
gt; With this information, I want to be able to find all the cells that have
gt; quot;1Cquot; and return with the date that corresponds to it. For example: quot;1Cquot;
gt; is
gt; in both the dates 3/1/05 and 3/3/05. I want a formula that will retrieve
gt; those two dates. And if there aren't any quot;1Cquot;, then I want to get a blank
gt; cell or a N/A
I have dates from B1:B10 and underneath each date it has either blank cells
or it has quot;1Cquot; , quot;2Cquot;, quot;3Cquot; underneath it. Now I want to retrieve all the
dates that have quot;1Cquot; underneath it.

quot;Biffquot; wrote:

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

gt;I have dates from B1:B10 and underneath each date it has either blank cells
gt;or it has quot;1Cquot; , quot;2Cquot;, quot;3Cquot; underneath it. Now I want to retrieve all the
gt;dates that have quot;1Cquot; 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

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

Yes, I have dates 3/1/06 - 3/7/06 in one row B1:H1. And in B2:H2 has quot;1Cquot;,
quot;2Cquot;, quot;3Cquot; and blank cells. In cell D6, I want to create a formula that will
give me the date that corresponds with quot;1Cquot;. For example if quot;1Cquot; 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/20063/2/20063/3/20063/4/20063/5/20063/6/20063/7/20061C2C3C

Try this:

=INDEX(B1:H1,MATCH(quot;1Cquot;,B2:H2,0))

Format the cell as DATE.

Biff

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