Hlookup, Vlookup and IF in combination??
 

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

Hlookup, Vlookup and IF in combination??
 

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

Hlookup, Vlookup and IF in combination??
 

=INDEX(D1:BD1,MIN(IF(D2:BD2<"",COLUMN(D2:BD2)-COLUMN(D2)+1)))

which is an array formula, so commit with Ctrl-Shift-Enter

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"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

Hlookup, Vlookup and IF in combination??
 

OK, so I replaced the 10^10 with the value I put into my test, and it worked.
You've got some skills!
What is the 10^10 meant to do?

"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

Hlookup, Vlookup and IF in combination??
 

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

Hlookup, Vlookup and IF in combination??
 

10^10 is 10 to the 10th power or 10,000,000,000

The way that LOOKUP works is if the lookup_value (10^10 or 10,000,000,000)
is not found the result is the LAST numeric value in the range that is less
than the lookup_value. As per the OP, the range would only contain one
numeric entry so that entry is the LAST numeric value in the range and is
more than likely less than the lookup_value.

Basically, the lookup_value (10^10 or 10,000,000,000) is an arbitrarily huge
number that is "guaranteed" to be greater than any numeric value in the
range thus ensuring the desired result.

Biff

"Sean Timmons" wrote in message
...
OK, so I replaced the 10^10 with the value I put into my test, and it
worked.
You've got some skills!
What is the 10^10 meant to do?

"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

Hlookup, Vlookup and IF in combination??
 

See my reply to Sean about 10^10.

Here's a small sample file that demonstrates this:

lookup_headers.xls 13.5kb

http://cjoint.com/?bdaBfZcblw

See if that helps.

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

Hlookup, Vlookup and IF in combination??
 

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

Hlookup, Vlookup and IF in combination??
 

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

Hlookup, Vlookup and IF in combination??
 

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

Hlookup, Vlookup and IF in combination??
 

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

Hlookup, Vlookup and IF in combination??
 

Thanks Biff. As I tried to relate before, those of you "out there" that
offer of your time and expertise to help those of us less familiar or
experienced with the capabilities of Excel are really appreciated.

Ron.

"T. Valko" wrote:

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