Complex multi criteria lookup and count formula???

I need a lookup formula or something similar to display data in "table 3" in Sheet 2 when I enter the date in the highlighted box. Each date contains different data symbols in "table 1" on sheet 1 and the value of each symbol is in "table 2" in cells C13 to D19 on sheet 1.
Anyway, I have a little bit more explaination in the book "Demo Data" below.

TIA

To avoid need for repetitive calcs I would advise the following:

C4: =MATCH($B4,Sheet1!$B$3:$AF$3,0)
D4: =COUNTIF(INDEX(Sheet1!$B$4:$AF$10,0,C4),"?*")
(assumes matrix is populated with text strings per the sample)

To then populate results:

B6: 
=IF(ROWS(B$6:B6)>$D$4,"",INDEX(Sheet1!$B$1:$AF$10,SMALL(IF(INDEX(Sheet1!$B$4:$AF$10,0,$C$4)<>"",ROW($B$4:$AF$10)),ROWS(B$6:B6)),$C$4))
committed with CTRL + SHIFT + ENTER
copy down to B12

C6:
=IF($B6="","",INDEX(Sheet1!C$13:C$19,MATCH(Sheet2!$B6,Sheet1!$B$13:$B$19,0)))
copy down to C12 and across for D6:D12

Originally Posted by DonkeyOte

To avoid need for repetitive calcs I would advise the following:

C4: =MATCH($B4,Sheet1!$B$3:$AF$3,0)
D4: =COUNTIF(INDEX(Sheet1!$B$4:$AF$10,0,C4),"?*")
(assumes matrix is populated with text strings per the sample)

To then populate results:

B6: 
=IF(ROWS(B$6:B6)>$D$4,"",INDEX(Sheet1!$B$1:$AF$10,SMALL(IF(INDEX(Sheet1!$B$4:$AF$10,0,$C$4)<>"",ROW($B$4:$AF$10)),ROWS(B$6:B6)),$C$4))
committed with CTRL + SHIFT + ENTER
copy down to B12

C6:
=IF($B6="","",INDEX(Sheet1!C$13:C$19,MATCH(Sheet2!$B6,Sheet1!$B$13:$B$19,0)))
copy down to C12 and across for D6:D12

Thanks for the really quick reply.

What sheet are you saying to enter the formula for C4 and D4? If I was to enter them in Sheet 1 I would lose the letters for those cells?

Edit: I just figured out it was for sheet 2! Thanks so much again for all the help DonkeyOte!!!

Everything to be added to Sheet2... C4 & D4 just reduce the number of repetitive calcs you have to perform, repetitive calcs are obviously to the detriment of overall performance.

On the subject of performance, word of warning, Arrays used en masse should generally be avoided... in your sample there aren't too many but be wary of overusing.

Yes that sounds bad... I've helped a few people here with poor array performance - I recall one in particular going from 40 min calc time to around 10 secs, achieved by simply foregoing elegance for the sake of efficiency... do post up any significant problematic/bottlenecks if you want assistance in reworking... arrays are great in Moderation (this includes Sumproduct).

Generally speaking IMO Concatenation is the key to avoiding Arrays, eg:

A1:A1000 contains strings
B1:B1000 contains numbers
C1:C1000 contains strings
D1:D1000 contains numbers

Let's say we wanted to sum D where A began with the letter A, B was > 10 and C ended with "xy".... a typical approach would be:

=SUMPRODUCT(--(LEFT(A1:A1000,1)="A"),--(B1:B1000>10),--(RIGHT(C1:C1000,2)="xy"),D1:D1000)

On it's own this is fine but if we assumed we doing lots of these for various combinations of strings in A&C etc things would get very expensive very quickly...

A simple, far less elegant but far more efficient approach is to use concatenation, eg we add a concatenation column at source - merging the columns of interest:

E1: =A1&":"&(B1>10)&":"&C1
copied to E1000 - for our number test we use a Boolean return rather than the value

Simply by adding E we no longer need to use SUMPRODUCT, we can use the far more efficient SUMIF with Wildcards in our criteria string so as to handle our string tests of A & C etc...

=SUMIF(E1:E1000,"A*:TRUE:*xy",D1:D1000)

As I say just a quick example.

(For those ready to jump in with 2007 SUMIFS, yes I know, the above is an illustration of a concept )

and a very nice illustration, DonkeyOte, I might add

Originally Posted by JapanDave

Is there anywhere I can go (on the net) to learn some of the functions you are stating?

To be honest I picked up my knowledge from sites such as these, colleagues and XL Help... others may have suggestions ?

Originally Posted by JapanDave

Excel is very misunderstood here and there are not many people who are good at it, let alone classes to attend

Hmm, maybe I should relocate to Japan