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

mmr1

Board Regular
Joined
Aug 25, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
1Ref CodeActivity DescriptionUnitLengthwidthdepthNr.Total Qty
2A003ATrial Trenches- Utilities (Allowance @ 50%)m3430.718.4
3A047.1Geotextile for 2x1000mm dia. GRP Linem15.5115.5
4A023Gravel surround for sewer linem3124.050.219.72
5A019.1Pipe Laying -2x1000m12224
6A023Gravel surround for sewer linem31221124
7A005.1Transport of Exc. Mat. from stock pile(to and fro)m336136
8A024Gravel shifting and loadingm324124
9A022Backfilling for Utilitesm3364.050.2129.16
10A026Pipe Loading/Shifting Works-1000mm dia.m12448
11A021Excavation for Utilitiesm31061.5190
12A044Miscellaneous Utility work/Allowance included in ActivitiesN/A414.5236
13A022Backfilling for Utilitesm32230.2113.2
14A044Miscellaneous Utility work/Allowance included in ActivitiesN/A5.50.55113.025
15E001Allowable General Plant Per DaySum0.20.2
16E002Allowable Excavator for Shoring WorksDay0.20.2
17361.405
01-09-2020
Cell Formulas
RangeFormula
H2:H16H2=+PRODUCT(D2,E2,F2,G2)
H17H17=SUM(H2:H16)

Multiple Look up values in a date wise sheet.xlsx
ABCDEFGH
1Ref CodeActivity DescriptionUnitLengthwidthdepthNr.Total Qty
2A003ATrial Trenches- Utilities (Allowance @ 50%)m3430.718.4
3A047.1Geotextile for 2x1000mm dia. GRP Linem15.50010
4A023Gravel surround for sewer linem3124.050.219.72
5A019.1Pipe Laying -2x1000m120020
6A023Gravel surround for sewer linem3124.050.219.72
7A005.1Transport of Exc. Mat. from stock pile(to and fro)m3003610
8A024Gravel shifting and loadingm3002410
9A022Backfilling for Utilitesm3364.050.2129.16
10A026Pipe Loading/Shifting Works-1000mm dia.m120040
11A021Excavation for Utilitiesm31061.5190
12A044Miscellaneous Utility work/Allowance included in ActivitiesN/A414.5236
13A022Backfilling for Utilitesm3364.050.2129.16
14A044Miscellaneous Utility work/Allowance included in ActivitiesN/A414.5236
15E001Allowable General Plant Per DaySum0000.20
16E002Allowable Excavator for Shoring WorksDay0000.20
1701.09.2020 Total248.16
18A003ATrial Trenches- Utilities (Allowance @ 50%)m3430.718.4
19A047.1Geotextile for 2x1000mm dia. GRP Linem15.50010
20A023Gravel surround for sewer linem3124.050.219.72
21A019.1Pipe Laying -2x1000m120020
22A023Gravel surround for sewer linem3124.050.219.72
23A005.1Transport of Exc. Mat. from stock pile(to and fro)m3003610
24A024Gravel shifting and loadingm3002410
25A022Backfilling for Utilitesm3364.250.2130.6
26A026Pipe Loading/Shifting Works-1000mm dia.m120040
27A021Excavation for Utilitiesm31061.5190
28A044Miscellaneous Utility work/Allowance included in ActivitiesN/A414.5236
29A022Backfilling for Utilitesm3364.250.2130.6
30A044Miscellaneous Utility work/Allowance included in ActivitiesN/A414.5236
31E001Allowable General Plant Per DaySum0000.20
32E002Allowable Excavator for Shoring WorksDay0000.20
3302.09.2020 Total251.04
Monthly
Cell Formulas
RangeFormula
B2:B16B2=+IFERROR(VLOOKUP($A2,'01-09-2020'!$A$2:$K$16,2,FALSE),"")
C2:C16C2=+IFERROR(VLOOKUP($A2,'01-09-2020'!$A$2:$K$16,3,FALSE),"")
D2:D16D2=+IFERROR(VLOOKUP($A2,'01-09-2020'!$A$2:$K$16,4,FALSE),"")
E2:E16E2=+IFERROR(VLOOKUP($A2,'01-09-2020'!$A$2:$K$16,5,FALSE),"")
F2:F16F2=+IFERROR(VLOOKUP($A2,'01-09-2020'!$A$2:$K$16,6,FALSE),"")
G2:G16G2=+IFERROR(VLOOKUP($A2,'01-09-2020'!$A$2:$K$16,7,FALSE),"")
B18:B32B18=+IFERROR(VLOOKUP($A18,'02-09-2020'!$A$2:$K$16,2,FALSE),"")
C18:C32C18=+IFERROR(VLOOKUP($A18,'02-09-2020'!$A$2:$K$16,3,FALSE),"")
D18:D32D18=+IFERROR(VLOOKUP($A18,'02-09-2020'!$A$2:$K$16,4,FALSE),"")
E18:E32E18=+IFERROR(VLOOKUP($A18,'02-09-2020'!$A$2:$K$16,5,FALSE),"")
F18:F32F18=+IFERROR(VLOOKUP($A18,'02-09-2020'!$A$2:$K$16,6,FALSE),"")
G18:G32G18=+IFERROR(VLOOKUP($A18,'02-09-2020'!$A$2:$K$16,7,FALSE),"")
H2:H16,H18:H32H2=+PRODUCT(D2,E2,F2,G2)
H17,H33H17=SUM(H2:H16)
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,995
Office Version
  1. 365
Platform
  1. Windows

mmr1

Board Regular
Joined
Aug 25, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,995
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,974
Messages
5,575,308
Members
412,655
Latest member
habercio
Top