I have one sheet that has a schedule of shows. It lists a store number, and
the start and end date of the show in three separates columns. It then also
has things like show number, saleperson, etc.

I have a sheet which needs to reference this information. It only has a
sale date and a store number, as the only things that would match the
reference list.

I need to lookup the show number and salesperson for the referencing sheet,
and am trying to come up with a way to make it do the following (in plain
language):

if referencing store number is equal to list store number AND referencing
single date is within start to end range, then lookup column so and so.

Any ideas? Example listed below:

Sheet 1
Store Txn Date
Store 123 11-25-06
Store 234 11-25-06

Sheet 2
Store Start End Show Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally


The formula in Sheet 1 to call the Show Num would need to come back with
43455.

In theory, there is usually one show per store (in which case I could just
assume that a straight vlookup of store number would get me what I need).
But we are contemplating more than 1. However store number and date range
will always be unique, as there will never be more than one show at any store
during one date range.

Thx for any help. I know this is not the easiest to even follow.
--
Boris

Since one of your sample show nums starts with a zero I'm assuming these are
formatted as TEXT?

This data is in the range A2:B3 -
Store 123 11-25-06
Store 234 11-25-06

This data is on Sheet2 in the range A1:E3 (headers in row 1) -
Store Start End Show Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally

Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER) on Sheet1 cell C2:

=INDEX(Sheet2!D$2:D$3,MATCH(1,(Sheet2!A$2:A$3=A2)* (Sheet2!B$2:B$3<=B2)*(B2<=Sheet2!C$2:C$3),0))

Copy down as needed.

Biff

"BorisS" wrote in message
...
I have one sheet that has a schedule of shows. It lists a store number,
and
the start and end date of the show in three separates columns. It then
also
has things like show number, saleperson, etc.

I have a sheet which needs to reference this information. It only has a
sale date and a store number, as the only things that would match the
reference list.

I need to lookup the show number and salesperson for the referencing
sheet,
and am trying to come up with a way to make it do the following (in plain
language):

if referencing store number is equal to list store number AND referencing
single date is within start to end range, then lookup column so and so.

Any ideas? Example listed below:

Sheet 1
Store Txn Date
Store 123 11-25-06
Store 234 11-25-06

Sheet 2
Store Start End Show Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally


The formula in Sheet 1 to call the Show Num would need to come back with
43455.

In theory, there is usually one show per store (in which case I could just
assume that a straight vlookup of store number would get me what I need).
But we are contemplating more than 1. However store number and date range
will always be unique, as there will never be more than one show at any
store
during one date range.

Thx for any help. I know this is not the easiest to even follow.
--
Boris

Biff, thanks so much. I am getting a bit lost in what looks to what. Any
chance you can describe what the sections do in simple language? My sheets
are not named sheet1, 2, etc, so I am trying to map what you wrote to what I
have. Also, the show number is not a text, but I can make it so. I was just
random typing numbers. I don't think any start with 0.
--
Boris


"T. Valko" wrote:

Since one of your sample show nums starts with a zero I'm assuming these are
formatted as TEXT?

This data is in the range A2:B3 -
Store 123 11-25-06
Store 234 11-25-06

This data is on Sheet2 in the range A1:E3 (headers in row 1) -
Store Start End Show Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally

Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER) on Sheet1 cell C2:

=INDEX(Sheet2!D$2:D$3,MATCH(1,(Sheet2!A$2:A$3=A2)* (Sheet2!B$2:B$3<=B2)*(B2<=Sheet2!C$2:C$3),0))

Copy down as needed.

Biff

"BorisS" wrote in message
...
I have one sheet that has a schedule of shows. It lists a store number,
and
the start and end date of the show in three separates columns. It then
also
has things like show number, saleperson, etc.

I have a sheet which needs to reference this information. It only has a
sale date and a store number, as the only things that would match the
reference list.

I need to lookup the show number and salesperson for the referencing
sheet,
and am trying to come up with a way to make it do the following (in plain
language):

if referencing store number is equal to list store number AND referencing
single date is within start to end range, then lookup column so and so.

Any ideas? Example listed below:

Sheet 1
Store Txn Date
Store 123 11-25-06
Store 234 11-25-06

Sheet 2
Store Start End Show Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally


The formula in Sheet 1 to call the Show Num would need to come back with
43455.

In theory, there is usually one show per store (in which case I could just
assume that a straight vlookup of store number would get me what I need).
But we are contemplating more than 1. However store number and date range
will always be unique, as there will never be more than one show at any
store
during one date range.

Thx for any help. I know this is not the easiest to even follow.
--
Boris

Okay, I THINK I've figured out what this was supposed to be doing, but am a
bit stuck. Let me walk through it, and tell me where I am off:

It goes to a one column width, multiple row array to index it. This array
is the lookup value result I want (let's say salesperson). All references
are for the same height in all expressions.

The next part uses MATCH to find something, and this is where I get lost.
It seems to read as trying to find a "1" in an array that is defined as a
series of multiplications, using boolean, so that if they are not all true,
it is 0, and if true, 1. But I am not sure how it can use the result as an
lookup array, which is what the second expression of MATCH looks for (at
least per the help instructions).

So I am a bit confused. Thanks for any clarification. I assume this worked
on your end when you mirrored my setup?
--
Boris


"T. Valko" wrote:

Since one of your sample show nums starts with a zero I'm assuming these are
formatted as TEXT?

This data is in the range A2:B3 -
Store 123 11-25-06
Store 234 11-25-06

This data is on Sheet2 in the range A1:E3 (headers in row 1) -
Store Start End Show Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally

Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER) on Sheet1 cell C2:

=INDEX(Sheet2!D$2:D$3,MATCH(1,(Sheet2!A$2:A$3=A2)* (Sheet2!B$2:B$3<=B2)*(B2<=Sheet2!C$2:C$3),0))

Copy down as needed.

Biff

"BorisS" wrote in message
...
I have one sheet that has a schedule of shows. It lists a store number,
and
the start and end date of the show in three separates columns. It then
also
has things like show number, saleperson, etc.

I have a sheet which needs to reference this information. It only has a
sale date and a store number, as the only things that would match the
reference list.

I need to lookup the show number and salesperson for the referencing
sheet,
and am trying to come up with a way to make it do the following (in plain
language):

if referencing store number is equal to list store number AND referencing
single date is within start to end range, then lookup column so and so.

Any ideas? Example listed below:

Sheet 1
Store Txn Date
Store 123 11-25-06
Store 234 11-25-06

Sheet 2
Store Start End Show Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally


The formula in Sheet 1 to call the Show Num would need to come back with
43455.

In theory, there is usually one show per store (in which case I could just
assume that a straight vlookup of store number would get me what I need).
But we are contemplating more than 1. However store number and date range
will always be unique, as there will never be more than one show at any
store
during one date range.

Thx for any help. I know this is not the easiest to even follow.
--
Boris

You pretty much have it figured out. Let's see if I can make a diagram that
will help:

Index.....Match(1,(conditon1)*(condition2)*(condit ion3)
blue.........................TRUE..........TRUE... .......FALSE... = 0
green.......................FALSE........FALSE.... ....FALSE... = 0
red..........................TRUE..........TRUE... .......TRUE..... = 1
brown.....................TRUE..........FALSE..... ...TRUE......= 0

Match(1 returns 3 because the lookup_value 1 is in the 3rd position of the
lookup_array. That 3 is then passed to the Index function telling it we want
the value in the 3rd position of the indexed array and the result of the
formula would be red.

Now, with that being the case for that particular formula, you can probably
use a less complicated formula to get this result. I assumed based on your
posted example that some store numbers had leading 0's leading me to believe
that the store numbers were TEXT entries. There is a less complicated
function that will work for this type of situation BUT this function will
only return NUMERIC results, no TEXT.

I put together a small sample file that uses both formulas:

Boris.xls 13.5kb

http://cjoint.com/?mndM0p6kX0

Biff

"BorisS" wrote in message
...
Okay, I THINK I've figured out what this was supposed to be doing, but am
a
bit stuck. Let me walk through it, and tell me where I am off:

It goes to a one column width, multiple row array to index it. This array
is the lookup value result I want (let's say salesperson). All references
are for the same height in all expressions.

The next part uses MATCH to find something, and this is where I get lost.
It seems to read as trying to find a "1" in an array that is defined as a
series of multiplications, using boolean, so that if they are not all
true,
it is 0, and if true, 1. But I am not sure how it can use the result as
an
lookup array, which is what the second expression of MATCH looks for (at
least per the help instructions).

So I am a bit confused. Thanks for any clarification. I assume this
worked
on your end when you mirrored my setup?
--
Boris


"T. Valko" wrote:

Since one of your sample show nums starts with a zero I'm assuming these
are
formatted as TEXT?

This data is in the range A2:B3 -
Store 123 11-25-06
Store 234 11-25-06

This data is on Sheet2 in the range A1:E3 (headers in row 1) -
Store Start End Show Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally

Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER
(not just ENTER) on Sheet1 cell C2:

=INDEX(Sheet2!D$2:D$3,MATCH(1,(Sheet2!A$2:A$3=A2)* (Sheet2!B$2:B$3<=B2)*(B2<=Sheet2!C$2:C$3),0))

Copy down as needed.

Biff

"BorisS" wrote in message
...
I have one sheet that has a schedule of shows. It lists a store number,
and
the start and end date of the show in three separates columns. It then
also
has things like show number, saleperson, etc.

I have a sheet which needs to reference this information. It only has
a
sale date and a store number, as the only things that would match the
reference list.

I need to lookup the show number and salesperson for the referencing
sheet,
and am trying to come up with a way to make it do the following (in
plain
language):

if referencing store number is equal to list store number AND
referencing
single date is within start to end range, then lookup column so and so.

Any ideas? Example listed below:

Sheet 1
Store Txn Date
Store 123 11-25-06
Store 234 11-25-06

Sheet 2
Store Start End Show Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally


The formula in Sheet 1 to call the Show Num would need to come back
with
43455.

In theory, there is usually one show per store (in which case I could
just
assume that a straight vlookup of store number would get me what I
need).
But we are contemplating more than 1. However store number and date
range
will always be unique, as there will never be more than one show at any
store
during one date range.

Thx for any help. I know this is not the easiest to even follow.
--
Boris

It worked. Wonderful. Thanks. I was referencing the wrong column for one
of the dates. You're a start. Thanks, thanks, thanks.
--
Boris


"T. Valko" wrote:

You pretty much have it figured out. Let's see if I can make a diagram that
will help:

Index.....Match(1,(conditon1)*(condition2)*(condit ion3)
blue.........................TRUE..........TRUE... .......FALSE... = 0
green.......................FALSE........FALSE.... ....FALSE... = 0
red..........................TRUE..........TRUE... .......TRUE..... = 1
brown.....................TRUE..........FALSE..... ...TRUE......= 0

Match(1 returns 3 because the lookup_value 1 is in the 3rd position of the
lookup_array. That 3 is then passed to the Index function telling it we want
the value in the 3rd position of the indexed array and the result of the
formula would be red.

Now, with that being the case for that particular formula, you can probably
use a less complicated formula to get this result. I assumed based on your
posted example that some store numbers had leading 0's leading me to believe
that the store numbers were TEXT entries. There is a less complicated
function that will work for this type of situation BUT this function will
only return NUMERIC results, no TEXT.

I put together a small sample file that uses both formulas:

Boris.xls 13.5kb

http://cjoint.com/?mndM0p6kX0

Biff

"BorisS" wrote in message
...
Okay, I THINK I've figured out what this was supposed to be doing, but am
a
bit stuck. Let me walk through it, and tell me where I am off:

It goes to a one column width, multiple row array to index it. This array
is the lookup value result I want (let's say salesperson). All references
are for the same height in all expressions.

The next part uses MATCH to find something, and this is where I get lost.
It seems to read as trying to find a "1" in an array that is defined as a
series of multiplications, using boolean, so that if they are not all
true,
it is 0, and if true, 1. But I am not sure how it can use the result as
an
lookup array, which is what the second expression of MATCH looks for (at
least per the help instructions).

So I am a bit confused. Thanks for any clarification. I assume this
worked
on your end when you mirrored my setup?
--
Boris


"T. Valko" wrote:

Since one of your sample show nums starts with a zero I'm assuming these
are
formatted as TEXT?

This data is in the range A2:B3 -
Store 123 11-25-06
Store 234 11-25-06

This data is on Sheet2 in the range A1:E3 (headers in row 1) -
Store Start End Show Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally

Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER
(not just ENTER) on Sheet1 cell C2:

=INDEX(Sheet2!D$2:D$3,MATCH(1,(Sheet2!A$2:A$3=A2)* (Sheet2!B$2:B$3<=B2)*(B2<=Sheet2!C$2:C$3),0))

Copy down as needed.

Biff

"BorisS" wrote in message
...
I have one sheet that has a schedule of shows. It lists a store number,
and
the start and end date of the show in three separates columns. It then
also
has things like show number, saleperson, etc.

I have a sheet which needs to reference this information. It only has
a
sale date and a store number, as the only things that would match the
reference list.

I need to lookup the show number and salesperson for the referencing
sheet,
and am trying to come up with a way to make it do the following (in
plain
language):

if referencing store number is equal to list store number AND
referencing
single date is within start to end range, then lookup column so and so.

Any ideas? Example listed below:

Sheet 1
Store Txn Date
Store 123 11-25-06
Store 234 11-25-06

Sheet 2
Store Start End Show Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally


The formula in Sheet 1 to call the Show Num would need to come back
with
43455.

In theory, there is usually one show per store (in which case I could
just
assume that a straight vlookup of store number would get me what I
need).
But we are contemplating more than 1. However store number and date
range
will always be unique, as there will never be more than one show at any
store
during one date range.

Thx for any help. I know this is not the easiest to even follow.
--
Boris

was supposed to say "star", not "start".
--
Boris


"T. Valko" wrote:

You pretty much have it figured out. Let's see if I can make a diagram that
will help:

Index.....Match(1,(conditon1)*(condition2)*(condit ion3)
blue.........................TRUE..........TRUE... .......FALSE... = 0
green.......................FALSE........FALSE.... ....FALSE... = 0
red..........................TRUE..........TRUE... .......TRUE..... = 1
brown.....................TRUE..........FALSE..... ...TRUE......= 0

Match(1 returns 3 because the lookup_value 1 is in the 3rd position of the
lookup_array. That 3 is then passed to the Index function telling it we want
the value in the 3rd position of the indexed array and the result of the
formula would be red.

Now, with that being the case for that particular formula, you can probably
use a less complicated formula to get this result. I assumed based on your
posted example that some store numbers had leading 0's leading me to believe
that the store numbers were TEXT entries. There is a less complicated
function that will work for this type of situation BUT this function will
only return NUMERIC results, no TEXT.

I put together a small sample file that uses both formulas:

Boris.xls 13.5kb

http://cjoint.com/?mndM0p6kX0

Biff

"BorisS" wrote in message
...
Okay, I THINK I've figured out what this was supposed to be doing, but am
a
bit stuck. Let me walk through it, and tell me where I am off:

It goes to a one column width, multiple row array to index it. This array
is the lookup value result I want (let's say salesperson). All references
are for the same height in all expressions.

The next part uses MATCH to find something, and this is where I get lost.
It seems to read as trying to find a "1" in an array that is defined as a
series of multiplications, using boolean, so that if they are not all
true,
it is 0, and if true, 1. But I am not sure how it can use the result as
an
lookup array, which is what the second expression of MATCH looks for (at
least per the help instructions).

So I am a bit confused. Thanks for any clarification. I assume this
worked
on your end when you mirrored my setup?
--
Boris


"T. Valko" wrote:

Since one of your sample show nums starts with a zero I'm assuming these
are
formatted as TEXT?

This data is in the range A2:B3 -
Store 123 11-25-06
Store 234 11-25-06

This data is on Sheet2 in the range A1:E3 (headers in row 1) -
Store Start End Show Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally

Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER
(not just ENTER) on Sheet1 cell C2:

=INDEX(Sheet2!D$2:D$3,MATCH(1,(Sheet2!A$2:A$3=A2)* (Sheet2!B$2:B$3<=B2)*(B2<=Sheet2!C$2:C$3),0))

Copy down as needed.

Biff

"BorisS" wrote in message
...
I have one sheet that has a schedule of shows. It lists a store number,
and
the start and end date of the show in three separates columns. It then
also
has things like show number, saleperson, etc.

I have a sheet which needs to reference this information. It only has
a
sale date and a store number, as the only things that would match the
reference list.

I need to lookup the show number and salesperson for the referencing
sheet,
and am trying to come up with a way to make it do the following (in
plain
language):

if referencing store number is equal to list store number AND
referencing
single date is within start to end range, then lookup column so and so.

Any ideas? Example listed below:

Sheet 1
Store Txn Date
Store 123 11-25-06
Store 234 11-25-06

Sheet 2
Store Start End Show Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally


The formula in Sheet 1 to call the Show Num would need to come back
with
43455.

In theory, there is usually one show per store (in which case I could
just
assume that a straight vlookup of store number would get me what I
need).
But we are contemplating more than 1. However store number and date
range
will always be unique, as there will never be more than one show at any
store
during one date range.

Thx for any help. I know this is not the easiest to even follow.
--
Boris

You're welcome. Thanks for the feedback!

Biff

"BorisS" wrote in message
...
was supposed to say "star", not "start".
--
Boris


"T. Valko" wrote:

You pretty much have it figured out. Let's see if I can make a diagram
that
will help:

Index.....Match(1,(conditon1)*(condition2)*(condit ion3)
blue.........................TRUE..........TRUE... .......FALSE... = 0
green.......................FALSE........FALSE.... ....FALSE... = 0
red..........................TRUE..........TRUE... .......TRUE..... = 1
brown.....................TRUE..........FALSE..... ...TRUE......= 0

Match(1 returns 3 because the lookup_value 1 is in the 3rd position of
the
lookup_array. That 3 is then passed to the Index function telling it we
want
the value in the 3rd position of the indexed array and the result of the
formula would be red.

Now, with that being the case for that particular formula, you can
probably
use a less complicated formula to get this result. I assumed based on
your
posted example that some store numbers had leading 0's leading me to
believe
that the store numbers were TEXT entries. There is a less complicated
function that will work for this type of situation BUT this function will
only return NUMERIC results, no TEXT.

I put together a small sample file that uses both formulas:

Boris.xls 13.5kb

http://cjoint.com/?mndM0p6kX0

Biff

"BorisS" wrote in message
...
Okay, I THINK I've figured out what this was supposed to be doing, but
am
a
bit stuck. Let me walk through it, and tell me where I am off:

It goes to a one column width, multiple row array to index it. This
array
is the lookup value result I want (let's say salesperson). All
references
are for the same height in all expressions.

The next part uses MATCH to find something, and this is where I get
lost.
It seems to read as trying to find a "1" in an array that is defined as
a
series of multiplications, using boolean, so that if they are not all
true,
it is 0, and if true, 1. But I am not sure how it can use the result
as
an
lookup array, which is what the second expression of MATCH looks for
(at
least per the help instructions).

So I am a bit confused. Thanks for any clarification. I assume this
worked
on your end when you mirrored my setup?
--
Boris


"T. Valko" wrote:

Since one of your sample show nums starts with a zero I'm assuming
these
are
formatted as TEXT?

This data is in the range A2:B3 -
Store 123 11-25-06
Store 234 11-25-06

This data is on Sheet2 in the range A1:E3 (headers in row 1) -
Store Start End Show
Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally

Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER
(not just ENTER) on Sheet1 cell C2:

=INDEX(Sheet2!D$2:D$3,MATCH(1,(Sheet2!A$2:A$3=A2)* (Sheet2!B$2:B$3<=B2)*(B2<=Sheet2!C$2:C$3),0))

Copy down as needed.

Biff

"BorisS" wrote in message
...
I have one sheet that has a schedule of shows. It lists a store
number,
and
the start and end date of the show in three separates columns. It
then
also
has things like show number, saleperson, etc.

I have a sheet which needs to reference this information. It only
has
a
sale date and a store number, as the only things that would match
the
reference list.

I need to lookup the show number and salesperson for the referencing
sheet,
and am trying to come up with a way to make it do the following (in
plain
language):

if referencing store number is equal to list store number AND
referencing
single date is within start to end range, then lookup column so and
so.

Any ideas? Example listed below:

Sheet 1
Store Txn Date
Store 123 11-25-06
Store 234 11-25-06

Sheet 2
Store Start End Show
Num
S.Person
Store 123 11-20-06 11-30-06 453455
John
Store 234 11-22-06 12-3-06 098434
Sally


The formula in Sheet 1 to call the Show Num would need to come back
with
43455.

In theory, there is usually one show per store (in which case I
could
just
assume that a straight vlookup of store number would get me what I
need).
But we are contemplating more than 1. However store number and date
range
will always be unique, as there will never be more than one show at
any
store
during one date range.

Thx for any help. I know this is not the easiest to even follow.
--
Boris