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