Do a two way lookup and get the result in multiple columns

[Anuj]

Hi,

I have a worksheet with data on store sales:

StoreLoc Date Sales$
A 10/28/2005 $100
A 11/04/2005 $150
B 10/28/2005 $200
B 11/04/2005 $250
B 11/11/2005 $300
B 11/18/2005 $350
C 11/04/2005 $300
C 11/11/2005 $350
C 11/18/2005 $400
D 11/11/2005 $400
etc.

I wanted to do a two-way lookup on StoreLoc and Date and get the sales
$ in a new worksheet in the following table format:

Store 10/28/05 11/04/05
11/11/05 11/18/05
A $100 $150
B $200 $250
$300 $350
C $300
$350 $400
D
$400

I tried using index-match function, the formula in B2 was:
=index(STORELOC,match($A2,STORELOC,0),match(B$1,DATE,0))

but for some reason that only allows me to do a fill down in ColumnB
but doesn't work for a fill across. Please help.

Thanks,
Anuj.

 

[Anuj]

Ooops,

the result table didn't come out right... reposting

Hi,

I have a worksheet with data on store sales:

StoreLoc Date Sales$
A 10/28/2005 $100
A 11/04/2005 $150
B 10/28/2005 $200
B 11/04/2005 $250
B 11/11/2005 $300
B 11/18/2005 $350
C 11/04/2005 $300
C 11/11/2005 $350
C 11/18/2005 $400
D 11/11/2005 $400
etc.

I wanted to do a two-way lookup on StoreLoc and Date and get the sales
$ in a new worksheet in the following table format:

Store 10/28/05 11/04/05 11/11/05 11/18/05

A $100 $150
B $200 $250 $300
$350
C $300 $350
$400
D $400

 

[T. Valko]

See this screencap:

http://img412.imageshack.us/img412/5748/sumpvl4.jpg