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