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