In Same day multiple lookup values to be shown as it is figures in full overview table.

[mmr1]

Hi,

I am looking a formula to be shown as it is figures with multiple lookup reference codes in a one day sheet with out any helper column .

example:-I tried with Vlookup formula but it is taken a first figures only, if the same reference code again repeated at that time similarly shown first figures only.

I need same figures and values to be shown each and every reference code even a reference code repeated in a same day.


Thanks for the help,

Multiple Look up values in a date wise sheet.xlsx
	A	B	C	D	E	F	G	H
1	Ref Code	Activity Description	Unit	Length	width	depth	Nr.	Total Qty
2	A003A	Trial Trenches- Utilities (Allowance @ 50%)	m3	4	3	0.7	1	8.4
3	A047.1	Geotextile for 2x1000mm dia. GRP Line	m	15.5			1	15.5
4	A023	Gravel surround for sewer line	m3	12	4.05	0.2	1	9.72
5	A019.1	Pipe Laying -2x1000	m	12			2	24
6	A023	Gravel surround for sewer line	m3	12	2	1	1	24
7	A005.1	Transport of Exc. Mat. from stock pile(to and fro)	m3			36	1	36
8	A024	Gravel shifting and loading	m3			24	1	24
9	A022	Backfilling for Utilites	m3	36	4.05	0.2	1	29.16
10	A026	Pipe Loading/Shifting Works-1000mm dia.	m	12			4	48
11	A021	Excavation for Utilities	m3	10	6	1.5	1	90
12	A044	Miscellaneous Utility work/Allowance included in Activities	N/A	4	1	4.5	2	36
13	A022	Backfilling for Utilites	m3	22	3	0.2	1	13.2
14	A044	Miscellaneous Utility work/Allowance included in Activities	N/A	5.5	0.55	1	1	3.025
15	E001	Allowable General Plant Per Day	Sum				0.2	0.2
16	E002	Allowable Excavator for Shoring Works	Day				0.2	0.2
17								361.405
01-09-2020

Cell Formulas
Range	Formula
H2:H16	H2	=+PRODUCT(D2,E2,F2,G2)
H17	H17	=SUM(H2:H16)

Multiple Look up values in a date wise sheet.xlsx
	A	B	C	D	E	F	G	H
1	Ref Code	Activity Description	Unit	Length	width	depth	Nr.	Total Qty
2	A003A	Trial Trenches- Utilities (Allowance @ 50%)	m3	4	3	0.7	1	8.4
3	A047.1	Geotextile for 2x1000mm dia. GRP Line	m	15.5	0	0	1	0
4	A023	Gravel surround for sewer line	m3	12	4.05	0.2	1	9.72
5	A019.1	Pipe Laying -2x1000	m	12	0	0	2	0
6	A023	Gravel surround for sewer line	m3	12	4.05	0.2	1	9.72
7	A005.1	Transport of Exc. Mat. from stock pile(to and fro)	m3	0	0	36	1	0
8	A024	Gravel shifting and loading	m3	0	0	24	1	0
9	A022	Backfilling for Utilites	m3	36	4.05	0.2	1	29.16
10	A026	Pipe Loading/Shifting Works-1000mm dia.	m	12	0	0	4	0
11	A021	Excavation for Utilities	m3	10	6	1.5	1	90
12	A044	Miscellaneous Utility work/Allowance included in Activities	N/A	4	1	4.5	2	36
13	A022	Backfilling for Utilites	m3	36	4.05	0.2	1	29.16
14	A044	Miscellaneous Utility work/Allowance included in Activities	N/A	4	1	4.5	2	36
15	E001	Allowable General Plant Per Day	Sum	0	0	0	0.2	0
16	E002	Allowable Excavator for Shoring Works	Day	0	0	0	0.2	0
17		01.09.2020 Total						248.16
18	A003A	Trial Trenches- Utilities (Allowance @ 50%)	m3	4	3	0.7	1	8.4
19	A047.1	Geotextile for 2x1000mm dia. GRP Line	m	15.5	0	0	1	0
20	A023	Gravel surround for sewer line	m3	12	4.05	0.2	1	9.72
21	A019.1	Pipe Laying -2x1000	m	12	0	0	2	0
22	A023	Gravel surround for sewer line	m3	12	4.05	0.2	1	9.72
23	A005.1	Transport of Exc. Mat. from stock pile(to and fro)	m3	0	0	36	1	0
24	A024	Gravel shifting and loading	m3	0	0	24	1	0
25	A022	Backfilling for Utilites	m3	36	4.25	0.2	1	30.6
26	A026	Pipe Loading/Shifting Works-1000mm dia.	m	12	0	0	4	0
27	A021	Excavation for Utilities	m3	10	6	1.5	1	90
28	A044	Miscellaneous Utility work/Allowance included in Activities	N/A	4	1	4.5	2	36
29	A022	Backfilling for Utilites	m3	36	4.25	0.2	1	30.6
30	A044	Miscellaneous Utility work/Allowance included in Activities	N/A	4	1	4.5	2	36
31	E001	Allowable General Plant Per Day	Sum	0	0	0	0.2	0
32	E002	Allowable Excavator for Shoring Works	Day	0	0	0	0.2	0
33		02.09.2020 Total						251.04
Monthly

Cell Formulas
Range	Formula
B2:B16	B2	=+IFERROR(VLOOKUP($A2,'01-09-2020'!$A$2:$K$16,2,FALSE),"")
C2:C16	C2	=+IFERROR(VLOOKUP($A2,'01-09-2020'!$A$2:$K$16,3,FALSE),"")
D2:D16	D2	=+IFERROR(VLOOKUP($A2,'01-09-2020'!$A$2:$K$16,4,FALSE),"")
E2:E16	E2	=+IFERROR(VLOOKUP($A2,'01-09-2020'!$A$2:$K$16,5,FALSE),"")
F2:F16	F2	=+IFERROR(VLOOKUP($A2,'01-09-2020'!$A$2:$K$16,6,FALSE),"")
G2:G16	G2	=+IFERROR(VLOOKUP($A2,'01-09-2020'!$A$2:$K$16,7,FALSE),"")
B18:B32	B18	=+IFERROR(VLOOKUP($A18,'02-09-2020'!$A$2:$K$16,2,FALSE),"")
C18:C32	C18	=+IFERROR(VLOOKUP($A18,'02-09-2020'!$A$2:$K$16,3,FALSE),"")
D18:D32	D18	=+IFERROR(VLOOKUP($A18,'02-09-2020'!$A$2:$K$16,4,FALSE),"")
E18:E32	E18	=+IFERROR(VLOOKUP($A18,'02-09-2020'!$A$2:$K$16,5,FALSE),"")
F18:F32	F18	=+IFERROR(VLOOKUP($A18,'02-09-2020'!$A$2:$K$16,6,FALSE),"")
G18:G32	G18	=+IFERROR(VLOOKUP($A18,'02-09-2020'!$A$2:$K$16,7,FALSE),"")
H2:H16,H18:H32	H2	=+PRODUCT(D2,E2,F2,G2)
H17,H33	H17	=SUM(H2:H16)

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H
1	Ref Code	Activity Description	Unit	Length	width	depth	Nr.	Total Qty
2	A003A	Trial Trenches- Utilities (Allowance @ 50%)	m3	4	3	0.7	1	8.4
3	A047.1	Geotextile for 2x1000mm dia. GRP Line	m	15.5	0	0	1	15.5
4	A023	Gravel surround for sewer line	m3	12	4.05	0.2	1	9.72
5	A019.1	Pipe Laying -2x1000	m	12	0	0	2	24
6	A023	Gravel surround for sewer line	m3	12	2	1	1	24
7	A005.1	Transport of Exc. Mat. from stock pile(to and fro)	m3	0	0	36	1	36
8	A024	Gravel shifting and loading	m3	0	0	24	1	24
9	A022	Backfilling for Utilites	m3	36	4.05	0.2	1	29.16
10	A026	Pipe Loading/Shifting Works-1000mm dia.	m	12	0	0	4	48
11	A021	Excavation for Utilities	m3	10	6	1.5	1	90
12	A044	Miscellaneous Utility work/Allowance included in Activities	N/A	4	1	4.5	2	36
13	A022	Backfilling for Utilites	m3	22	3	0.2	1	13.2
14	A044	Miscellaneous Utility work/Allowance included in Activities	N/A	5.5	0.55	1	1	3.025
15	E001	Allowable General Plant Per Day	Sum	0	0	0	0.2	0.2
16	E002	Allowable Excavator for Shoring Works	Day	0	0	0	0.2	0.2
17		01.09.2020 Total						248.16
Roster

Cell Formulas
Range	Formula
B2:H16	B2	=INDEX('01-09-2020'!B$2:B$16,AGGREGATE(15,6,(ROW('01-09-2020'!$A$2:$A$16)-ROW('01-09-2020'!$A$2)+1)/('01-09-2020'!$A$2:$A$16=$A2),COUNTIFS($A$2:$A2,$A2)))
H17	H17	=SUM(O2:O16)

 

[mmr1]

Hi,

Thanks a lot Fluff, its working as in line what i exactly want very use full solution you're provided,



Thanks a lot once again,

 

[Fluff]

You're welcome & thanks for the feedback.