SOLVED

New Contributor

Combining data in a new table

I have a table with products, prices and then quantity by customer - I would like to create a new table with the total price of the products by customer. I know I can create a million vlookups of the product quantity multiplied by a vlookup of the price but I know there must be a better way. So for example Column A is paper, staples, pen, paper clips Column B is prices \$2.00, \$0.50, \$1,20, \$0.25 then the following columns are customers - so customer X has 5 paper and 10 pens and the following customers are in the following columns. I want the new table to show customer X with \$10, \$0, \$12.00 etc (so lookup customer quantity and multiply by price). I just need the suggestion on how to build this. Vlookups are too long - is there a command or function I should research that will help me make this? Thank you

4 Replies

Betreff: Combining data in a new table

A noble offer from Mr.Sergei Baklan

can only recommend it :)))

https://techcommunity.microsoft.com/t5/excel/look-up-in-table-based-on-4-values/m-p/1839794#M79568

To continue tinkering yourself

```XLOOKUP(
D14,
(\$A\$2:\$A\$9&\$B\$2:\$B\$9&\$C\$2:\$C\$9=A14&B14&C14)*\$D\$2:\$D\$9,
\$F\$2:\$F\$9,
"no such",
-1```

Hope it helped you

Nikolino

I know I don't know anything (Socrates)

best response confirmed by mariong (New Contributor)
Solution

RE: Combining data in a new table

Although I think Power Query is more appropriate here. Add a MS Excel file (without sensitive data) to your project. Knowing the Excel version also be an advantage. So you could get a much more concrete solution than the one I sent. Thank you for your understanding and patience Nikolino I know I don't know anything (Socrates)

RE: Combining data in a new table

@Nikolino I think you are 100% correct - so I have created a sample table as the information is confidential - so the first column is the product - then the price per product, the shipping per product and the extra per product - then all the customers by quantity - what I need is to create a new table with Customer 1 - bananas cost, bananas shipping, bananas extra cost, then oranges cost, oranges shipping, oranges extra (so I have the actual prices per category by customer rather than numbers) -thank you

RE: Combining data in a new table

Hope I understood the translation correctly. Please look at the inserted file, is the result in the direction you want?

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)