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

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

<tbody>
</tbody>

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

User 1

User 1

User 1

User 1

Date

productivity

hours

review

productivity

hours

review

productivity

hours

review

productivity

hours

review

5/1/2015

5/2/2015

5/3/2015

5/4/2015

5/5/2015

5/6/2015

5/7/2015

5/8/2015

5/9/2015

5/10/2015

5/11/2015

5/12/2015

5/13/2015

5/14/2015

5/15/2015

5/16/2015

5/17/2015

5/18/2015

5/19/2015

5/20/2015

5/21/2015

5/22/2015

<tbody>
</tbody>

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

Row\Col	A​	B​	C​	D​
1​	Date
2​	5/1/2015​
3​	Name	productivity	hours	Review
4​	user 1	506​	6​	85​
5​	user 2	563​	4​	35​
6​	user 3	429​	9​	76​
7​	user 4	742​	6​	27​
8​	user 5	166​	8​	94​
9​	user 6	636​	9​	86​
10​	user 7	209​	6​	26​
11​	user 8	741​	7​	87​
12​	user 9	453​	7​	83​
13​	user 10	384​	2​	20​
14​	user 11	749​	1​	36​
15​	user 12	507​	5​	69​
16​	user 13	328​	4​	11​
17​	user 14	618​	1​	85​
18​	user 15	539​	7​	46​
19​	user 16	410​	10​	44​
20​	user 17	218​	3​	25​
21​	user 18	286​	3​	74​

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

Row\Col	A​	B​	C​	D​
1​		User 1
2​	Date	productivity	hours	review
3​	5/1/2015​	506​	6​	85​
4​

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