Let's look at a small example:

............A.............B............C.......... ..D....
1......1/1/07.....1/2/07.....1/3/07.....1/4/07
2.........0.............0.............5........... ..0....

=LOOKUP(2,1/(A2:D20),A1:D1)

The lookup_value is 2. If the lookup_value is not found the result will be
the *LAST* numeric value in the lookup_vector that is less than the
lookup_value.

The lookup_vector is an array that is generated by this statement:

1/(A2:D20)

(A2:D20) will generate an array of either TRUE or FALSE.

A20 = FALSE
B20 = FALSE
C20 = TRUE
D20 = FALSE

These logical values are then coerced into numbers by the math operation of
dividing. In Excel these logical values (also called boolean values) have a
value of 1 for TRUE and 0 for FALSE

1/(A20) = 1/(FALSE) = 1/0 = #DIV/0!
1/(B20) = 1/(FALSE) = 1/0 = #DIV/0!
1/(C20) = 1/(TRUE) = 1/1 = 1
1/(D20) = 1/(FALSE) = 1/0 = #DIV/0!

At this point the formula would look like this:

=LOOKUP(2,{#DIV/0!,#DIV/0!,1,#DIV/0!},A1:D1)

Now, with the lookup_value being 2, the *LAST* numeric value in the
lookup_vector that is less than the lookup_value is 1.

The 1 is in the 3rd position of the lookup_vector so the result of the
formula is the value that's in the 3rd position of the result_vector (A1:D1)
which is whatever is in cell C1. It would look like this:

............A...............B..............C...... ........D....
1......1/1/07.......1/2/07.......1/3/07.......1/4/07
........#DIV/0!....#DIV/0!........1..........#DIV/0!

So:

=LOOKUP(2,1/(A2:D20),A1:D1)

Returns (formatted as DATE): 1/3/07

Biff

"ronnomad" wrote in message
...
Biff,

Works like a charm. Would you mind explaining what the 2,1/ is doing (for
future reference) ?

Thanks again. This has saved me from the tedium of looking up almost
1,000
items.

Ron

"T. Valko" wrote:

Ok, try something like this:

=IF(C2=1,LOOKUP(2,1/(D2:J20),D$1:J$1),"")

Biff

"ronnomad" wrote in message
...
Biff

Some of them contain zero's. The way this spreadsheet works is that I
take
mass data and break it down by item number. I pull the data from
another
sheet using a SUMPRODUCT formula. I enter the formula into every cell
in
a
particular column and then convert the results to values.

Ron

"T. Valko" wrote:

In cells where there were no quantities ordered, are these cells EMPTY
or
might they contain zero's?

Biff

"ronnomad" wrote in message
...
Biff,

I see that Sean got it to work by putting in some value but, when I
duplicate your formula with my cells, each response (answer) comes
up
Dec-25
(which is the last column). What did I do wrong? And, what does
the
10^10
do?

Thanks again. You guys Rock!!

Ron

"T. Valko" wrote:

Try something like this:

D1:J1 = date headers

=IF(C2=1,LOOKUP(10^10,D2:J2,D$1:J$1),"")

Format as DATE

Biff

"ronnomad" wrote in message
...
I have data that is about 2000 rows and 55 columns. 52 of the
columms
are
headed with a week starting date. The other columns have product
names,
number and number of times ordered (this number is attained by
counting
the
times a quantity appears in a dated column). The 52 dated
columns
have
the
quantity ordered but each items is not ordered every week. What
I
would
like
to do is:

For the items that have been ordered only once, find the date in
the
heading
and place that date in a column beside the product name.

Is there a way to write a formula (or macro) that would
basically
say,
"if
the quantity in columm C (the number of times ordered) is equal
to
1,
look
across the dated columns until a value greater than zero is found
and
then
copy the date at the head of that column into the cell where the
formula
is".

Thanks & Happy New Year

Ron R