Need help for multiple criteria vlookup

[Penny Bangalore]

Hi Folks,

Need your help to to build a formula for multiple criteria. so below is what i can explain my best.

i have my data in sheet 3 as below [TABLE="width: 1314"]
<tbody>[TR]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5/2/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5/3/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5/4/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]productivity[/TD]
[TD]hours[/TD]
[TD]Review[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]productivity[/TD]
[TD]hours[/TD]
[TD]Review[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]productivity[/TD]
[TD]hours[/TD]
[TD]Review[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]productivity[/TD]
[TD]hours[/TD]
[TD]Review[/TD]
[/TR]
[TR]
[TD]user 1[/TD]
[TD]506[/TD]
[TD]6[/TD]
[TD]85[/TD]
[TD][/TD]
[TD]user 1[/TD]
[TD]190[/TD]
[TD]179[/TD]
[TD]122[/TD]
[TD][/TD]
[TD]user 1[/TD]
[TD]225[/TD]
[TD]229[/TD]
[TD]292[/TD]
[TD][/TD]
[TD]user 1[/TD]
[TD]567[/TD]
[TD]508[/TD]
[TD]517[/TD]
[/TR]
[TR]
[TD]user 2[/TD]
[TD]563[/TD]
[TD]4[/TD]
[TD]35[/TD]
[TD][/TD]
[TD]user 2[/TD]
[TD]101[/TD]
[TD]187[/TD]
[TD]111[/TD]
[TD][/TD]
[TD]user 2[/TD]
[TD]248[/TD]
[TD]300[/TD]
[TD]251[/TD]
[TD][/TD]
[TD]user 2[/TD]
[TD]552[/TD]
[TD]547[/TD]
[TD]532[/TD]
[/TR]
[TR]
[TD]user 3[/TD]
[TD]429[/TD]
[TD]9[/TD]
[TD]76[/TD]
[TD][/TD]
[TD]user 3[/TD]
[TD]199[/TD]
[TD]102[/TD]
[TD]101[/TD]
[TD][/TD]
[TD]user 3[/TD]
[TD]300[/TD]
[TD]211[/TD]
[TD]279[/TD]
[TD][/TD]
[TD]user 3[/TD]
[TD]527[/TD]
[TD]523[/TD]
[TD]506[/TD]
[/TR]
[TR]
[TD]user 4[/TD]
[TD]742[/TD]
[TD]6[/TD]
[TD]27[/TD]
[TD][/TD]
[TD]user 4[/TD]
[TD]173[/TD]
[TD]132[/TD]
[TD]187[/TD]
[TD][/TD]
[TD]user 4[/TD]
[TD]211[/TD]
[TD]272[/TD]
[TD]224[/TD]
[TD][/TD]
[TD]user 4[/TD]
[TD]592[/TD]
[TD]553[/TD]
[TD]572[/TD]
[/TR]
[TR]
[TD]user 5[/TD]
[TD]166[/TD]
[TD]8[/TD]
[TD]94[/TD]
[TD][/TD]
[TD]user 5[/TD]
[TD]185[/TD]
[TD]137[/TD]
[TD]130[/TD]
[TD][/TD]
[TD]user 5[/TD]
[TD]251[/TD]
[TD]256[/TD]
[TD]259[/TD]
[TD][/TD]
[TD]user 5[/TD]
[TD]505[/TD]
[TD]593[/TD]
[TD]513[/TD]
[/TR]
[TR]
[TD]user 6[/TD]
[TD]636[/TD]
[TD]9[/TD]
[TD]86[/TD]
[TD][/TD]
[TD]user 6[/TD]
[TD]175[/TD]
[TD]145[/TD]
[TD]148[/TD]
[TD][/TD]
[TD]user 6[/TD]
[TD]250[/TD]
[TD]231[/TD]
[TD]285[/TD]
[TD][/TD]
[TD]user 6[/TD]
[TD]593[/TD]
[TD]564[/TD]
[TD]556[/TD]
[/TR]
[TR]
[TD]user 7[/TD]
[TD]209[/TD]
[TD]6[/TD]
[TD]26[/TD]
[TD][/TD]
[TD]user 7[/TD]
[TD]176[/TD]
[TD]106[/TD]
[TD]140[/TD]
[TD][/TD]
[TD]user 7[/TD]
[TD]274[/TD]
[TD]235[/TD]
[TD]251[/TD]
[TD][/TD]
[TD]user 7[/TD]
[TD]585[/TD]
[TD]523[/TD]
[TD]514[/TD]
[/TR]
[TR]
[TD]user 8[/TD]
[TD]741[/TD]
[TD]7[/TD]
[TD]87[/TD]
[TD][/TD]
[TD]user 8[/TD]
[TD]126[/TD]
[TD]116[/TD]
[TD]181[/TD]
[TD][/TD]
[TD]user 8[/TD]
[TD]296[/TD]
[TD]292[/TD]
[TD]227[/TD]
[TD][/TD]
[TD]user 8[/TD]
[TD]526[/TD]
[TD]500[/TD]
[TD]585[/TD]
[/TR]
[TR]
[TD]user 9[/TD]
[TD]453[/TD]
[TD]7[/TD]
[TD]83[/TD]
[TD][/TD]
[TD]user 9[/TD]
[TD]126[/TD]
[TD]190[/TD]
[TD]127[/TD]
[TD][/TD]
[TD]user 9[/TD]
[TD]227[/TD]
[TD]266[/TD]
[TD]285[/TD]
[TD][/TD]
[TD]user 9[/TD]
[TD]550[/TD]
[TD]567[/TD]
[TD]518[/TD]
[/TR]
[TR]
[TD]user 10[/TD]
[TD]384[/TD]
[TD]2[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]user 10[/TD]
[TD]122[/TD]
[TD]102[/TD]
[TD]107[/TD]
[TD][/TD]
[TD]user 10[/TD]
[TD]272[/TD]
[TD]256[/TD]
[TD]290[/TD]
[TD][/TD]
[TD]user 10[/TD]
[TD]566[/TD]
[TD]558[/TD]
[TD]597[/TD]
[/TR]
[TR]
[TD]user 11[/TD]
[TD]749[/TD]
[TD]1[/TD]
[TD]36[/TD]
[TD][/TD]
[TD]user 11[/TD]
[TD]118[/TD]
[TD]140[/TD]
[TD]159[/TD]
[TD][/TD]
[TD]user 11[/TD]
[TD]249[/TD]
[TD]300[/TD]
[TD]280[/TD]
[TD][/TD]
[TD]user 11[/TD]
[TD]562[/TD]
[TD]578[/TD]
[TD]558[/TD]
[/TR]
[TR]
[TD]user 12[/TD]
[TD]507[/TD]
[TD]5[/TD]
[TD]69[/TD]
[TD][/TD]
[TD]user 12[/TD]
[TD]129[/TD]
[TD]116[/TD]
[TD]190[/TD]
[TD][/TD]
[TD]user 12[/TD]
[TD]261[/TD]
[TD]289[/TD]
[TD]295[/TD]
[TD][/TD]
[TD]user 12[/TD]
[TD]578[/TD]
[TD]527[/TD]
[TD]527[/TD]
[/TR]
[TR]
[TD]user 13[/TD]
[TD]328[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]user 13[/TD]
[TD]133[/TD]
[TD]197[/TD]
[TD]110[/TD]
[TD][/TD]
[TD]user 13[/TD]
[TD]224[/TD]
[TD]295[/TD]
[TD]228[/TD]
[TD][/TD]
[TD]user 13[/TD]
[TD]522[/TD]
[TD]508[/TD]
[TD]568[/TD]
[/TR]
[TR]
[TD]user 14[/TD]
[TD]618[/TD]
[TD]1[/TD]
[TD]85[/TD]
[TD][/TD]
[TD]user 14[/TD]
[TD]199[/TD]
[TD]143[/TD]
[TD]197[/TD]
[TD][/TD]
[TD]user 14[/TD]
[TD]205[/TD]
[TD]296[/TD]
[TD]275[/TD]
[TD][/TD]
[TD]user 14[/TD]
[TD]574[/TD]
[TD]585[/TD]
[TD]562[/TD]
[/TR]
[TR]
[TD]user 15[/TD]
[TD]539[/TD]
[TD]7[/TD]
[TD]46[/TD]
[TD][/TD]
[TD]user 15[/TD]
[TD]112[/TD]
[TD]138[/TD]
[TD]174[/TD]
[TD][/TD]
[TD]user 15[/TD]
[TD]267[/TD]
[TD]217[/TD]
[TD]225[/TD]
[TD][/TD]
[TD]user 15[/TD]
[TD]560[/TD]
[TD]562[/TD]
[TD]585[/TD]
[/TR]
[TR]
[TD]user 16[/TD]
[TD]410[/TD]
[TD]10[/TD]
[TD]44[/TD]
[TD][/TD]
[TD]user 16[/TD]
[TD]189[/TD]
[TD]158[/TD]
[TD]167[/TD]
[TD][/TD]
[TD]user 16[/TD]
[TD]202[/TD]
[TD]300[/TD]
[TD]251[/TD]
[TD][/TD]
[TD]user 16[/TD]
[TD]576[/TD]
[TD]534[/TD]
[TD]578[/TD]
[/TR]
[TR]
[TD]user 17[/TD]
[TD]218[/TD]
[TD]3[/TD]
[TD]25[/TD]
[TD][/TD]
[TD]user 17[/TD]
[TD]107[/TD]
[TD]192[/TD]
[TD]171[/TD]
[TD][/TD]
[TD]user 17[/TD]
[TD]258[/TD]
[TD]291[/TD]
[TD]236[/TD]
[TD][/TD]
[TD]user 17[/TD]
[TD]530[/TD]
[TD]579[/TD]
[TD]545[/TD]
[/TR]
[TR]
[TD]user 18[/TD]
[TD]286[/TD]
[TD]3[/TD]
[TD]74[/TD]
[TD][/TD]
[TD]user 18[/TD]
[TD]117[/TD]
[TD]116[/TD]
[TD]160[/TD]
[TD][/TD]
[TD]user 18[/TD]
[TD]241[/TD]
[TD]224[/TD]
[TD]280[/TD]
[TD][/TD]
[TD]user 18[/TD]
[TD]579[/TD]
[TD]561[/TD]
[TD]597[/TD]
[/TR]
</tbody>[/TABLE]


what i need is in sheet 2 is the productivity and review and hour counts based on date and user in below format

[TABLE="width: 1100"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 3"]User 1[/TD]
[TD][/TD]
[TD="colspan: 3"]User 1[/TD]
[TD][/TD]
[TD="colspan: 3"]User 1[/TD]
[TD][/TD]
[TD="colspan: 3"]User 1[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]productivity[/TD]
[TD]hours[/TD]
[TD]review[/TD]
[TD][/TD]
[TD]productivity[/TD]
[TD]hours[/TD]
[TD]review[/TD]
[TD][/TD]
[TD]productivity[/TD]
[TD]hours[/TD]
[TD]review[/TD]
[TD][/TD]
[TD]productivity[/TD]
[TD]hours[/TD]
[TD]review[/TD]
[/TR]
[TR]
[TD]5/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/2/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/3/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/4/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/5/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/6/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/7/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/8/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/9/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/10/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/11/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/12/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/13/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/14/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/15/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/16/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/17/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/18/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/19/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/20/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/21/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/22/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

some one please help me with a multiple criteria vlookup.some one please help me with a multiple criteria vlookup.

 

[Penny Bangalore]

No reply.... is it so tough ????

 

[Aladin Akyurek]

Since you ask...

=VLOOKUP(A2,INDIRECT("T"&TEXT(A2,"mmddyyyy")),2,0)

where A2 is a date and T05012015 is the name of the table that you have on 5/1/2015.

Your data layout is such that it won't be that easy to process...

 

[VectorW2]

have you tried a dsum formula?

 

[Penny Bangalore]

Hi Aladin,

i tried your formula, but it did not work. my data is in sheet 3 and my formula should be in sheet 2 to get the data from sheet 3 based on date and user criteria please help

 

[Penny Bangalore]

Hi Vectorw2,

No i did not.. can you help me with this.. please

 

[VectorW2]

I will try, for the dsum formula to work, you need to have a header row, "name/productivity/hours/review", but you need to add your date as that is part of your criteria profile. "name/productivity/hours/review/date". Are you able to manipulate your data to include the date as part of your data?

 

[VectorW2]

The way dsum works is(database,field,criteria) so for example:

=dsum(sheet3!a1:e111,"productivity",Sheet1!a1:e2)

<o></o>

This formula is looking at your sheet3!'sdata so you can enter this formula in the desired cell on your sheet2! <o></o>


Please note with the dsum formula, you must choose your criteria, so pick aplace, "for this example shee1! cells a1:e2", you will enter yourcriteria headers in cells a1:e1 "name/productivity/hours/review/date"and in cells a2:e2 enter your specifics. Use =sheet2a1 in cell a2 on sheet1!,then drag the formula over to populate the remaining cells. Should looksomething like: "user 1/506/6/85/5-1-2015". Also note, the criteria portionof your formula can be wherever you want it. Depending on what you want, this would give you the value of 506 for theproductivity of user 1 on May 1, 2015. If you want something else then change thecriteria range in your formula. I hopethis helps.<o></o>

 

[Aladin Akyurek]

Since you ask...

=VLOOKUP(A2,INDIRECT("T"&TEXT(A2,"mmddyyyy")),2,0)

where A2 is a date and T05012015 is the name of the table that you have on 5/1/2015.

Your data layout is such that it won't be that easy to process...

Hi Aladin,

i tried your formula, but it did not work. my data is in sheet 3 and my formula should be in sheet 2 to get the data from sheet 3 based on date and user criteria please help

You should try a bit better what you are given....

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Date[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

5/1/2015​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]Name[/td][td]productivity[/td][td]hours[/td][td]Review[/td][/tr]

[tr][td]

4​

[/td][td]user 1[/td][td]

506​

[/td][td]

6​

[/td][td]

85​

[/td][/tr]

[tr][td]

5​

[/td][td]user 2[/td][td]

563​

[/td][td]

4​

[/td][td]

35​

[/td][/tr]

[tr][td]

6​

[/td][td]user 3[/td][td]

429​

[/td][td]

9​

[/td][td]

76​

[/td][/tr]

[tr][td]

7​

[/td][td]user 4[/td][td]

742​

[/td][td]

6​

[/td][td]

27​

[/td][/tr]

[tr][td]

8​

[/td][td]user 5[/td][td]

166​

[/td][td]

8​

[/td][td]

94​

[/td][/tr]

[tr][td]

9​

[/td][td]user 6[/td][td]

636​

[/td][td]

9​

[/td][td]

86​

[/td][/tr]

[tr][td]

10​

[/td][td]user 7[/td][td]

209​

[/td][td]

6​

[/td][td]

26​

[/td][/tr]

[tr][td]

11​

[/td][td]user 8[/td][td]

741​

[/td][td]

7​

[/td][td]

87​

[/td][/tr]

[tr][td]

12​

[/td][td]user 9[/td][td]

453​

[/td][td]

7​

[/td][td]

83​

[/td][/tr]

[tr][td]

13​

[/td][td]user 10[/td][td]

384​

[/td][td]

2​

[/td][td]

20​

[/td][/tr]

[tr][td]

14​

[/td][td]user 11[/td][td]

749​

[/td][td]

1​

[/td][td]

36​

[/td][/tr]

[tr][td]

15​

[/td][td]user 12[/td][td]

507​

[/td][td]

5​

[/td][td]

69​

[/td][/tr]

[tr][td]

16​

[/td][td]user 13[/td][td]

328​

[/td][td]

4​

[/td][td]

11​

[/td][/tr]

[tr][td]

17​

[/td][td]user 14[/td][td]

618​

[/td][td]

1​

[/td][td]

85​

[/td][/tr]

[tr][td]

18​

[/td][td]user 15[/td][td]

539​

[/td][td]

7​

[/td][td]

46​

[/td][/tr]

[tr][td]

19​

[/td][td]user 16[/td][td]

410​

[/td][td]

10​

[/td][td]

44​

[/td][/tr]

[tr][td]

20​

[/td][td]user 17[/td][td]

218​

[/td][td]

3​

[/td][td]

25​

[/td][/tr]

[tr][td]

21​

[/td][td]user 18[/td][td]

286​

[/td][td]

3​

[/td][td]

74​

[/td][/tr]
[/table]

A3:D21 is name as T05012015 (modeled after 5/1/2015).

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]User 1[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Date[/td][td]productivity[/td][td]hours[/td][td]review[/td][/tr]

[tr][td]

3​

[/td][td]

5/1/2015​

[/td][td]

506​

[/td][td]

6​

[/td][td]

85​

[/td][/tr]

[tr][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
B3:

=VLOOKUP($B1,INDIRECT("T"&TEXT(A3,"mmddyyyy")),2,0)

C3:

=VLOOKUP($B1,INDIRECT("T"&TEXT(A3,"mmddyyyy")),3,0)

D3:

=VLOOKUP($B1,INDIRECT("T"&TEXT(A3,"mmddyyyy")),4,0)

 

[Penny Bangalore]

Hi Aladin,

I appreciate you and thank you that i am half way through as that the formula is working fine.. but the data is picking as user wise .. i want my data to be picked as both user wise and data wise like i said the i am consolidating my data from another work book dates in rows and users in columns.. its my biggest project so i need your help.

what i did so far is..
i have created a table named T05012015 as you instructed and i have applied your formula.. it works fine.. but it should also consider date criteria.

thanks for your time
penny