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,
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) |
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) |