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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
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
53,314
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,364
Messages
5,601,187
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top