Excel : using VLOOKUP to aggregate several values

0
votes

I am doing quotations in Excel for my company and I am trying to find a way to do 2 things:

  • aggregate several quantities of the same product based on its reference number
  • return several values for the same references (i.e. different prices)

I was thinking about using VLOOKUP but I cannot find a way to make it sum or return multiple values.

Table outline in the picture attached here, the wanted result would be: - for the 1st function: sum up the table into 3 lines with aggregated quantities for products X Y Z - for the 2nd function: sum up the table into 4 lines with the prices for products X Y Z (X having 2 different prices in the sample data, but I would need the function to be able to return as many lines as there are different prices for the same product)

enter image description here

Thank you very much for you help!

Kind regards, Antoine

Edit: to get a better idea of what I try to achieve, here is another picture: 2nd pic with annotations Quantities of the same product have to be aggregated, except if the unit price is different. Could price difference be based on a subreference to be added to the first table? That would need the vlookup to match 2 values to return a result, is that feasible? Noting that I do not have control over the source data for unit price, meaning that reference and subreference would have to be in different columns rather than concatenated.

The goal is to produce a recap table rather than filter out single values as suggested in the answer.

1
excelexcel-formulavlookup
You want a pivot table - Scott Craner
I tried that at first but since we have observations and descriptions in-between certain lines, it does not let me create one - Antoine Muller
I would add a column to use vlookup to test each one, with an if() to put a result of 1 or 0, then a sumproduct()... - Solar Mike

1 Answers

0
votes

So, try this:

Cell B10 is the product you want to look at then G2 is the test if the product on that row matches. Finally, the sumproduct calculates the total, but you can change which column it looks at, I just chose the total column.

Now showing use of data validation from a list:

enter image description here