concatenated lookup?

[Guest]

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.

 

[T. Valko]

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$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

 

[T. Valko]

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

Index.....Match(1,(conditon1)*(condition2)*(condition3)
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

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

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