SOLVED

Combining data in a new table

%3CLINGO-SUB%20id%3D%22lingo-sub-1844119%22%20slang%3D%22en-US%22%3ECombining%20data%20in%20a%20new%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1844119%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20with%20products%2C%20prices%20and%20then%20quantity%20by%20customer%20-%20I%20would%20like%20to%20create%20a%20new%20table%20with%20the%20total%20price%20of%20the%20products%20by%20customer.%20I%20know%20I%20can%20create%20a%20million%20vlookups%20of%20the%20product%20quantity%20multiplied%20by%20a%20vlookup%20of%20the%20price%20but%20I%20know%20there%20must%20be%20a%20better%20way.%20So%20for%20example%20Column%20A%20is%20paper%2C%20staples%2C%20pen%2C%20paper%20clips%20Column%20B%20is%20prices%20%242.00%2C%20%240.50%2C%20%241%2C20%2C%20%240.25%20then%20the%20following%20columns%20are%20customers%20-%20so%20customer%20X%20has%205%20paper%20and%2010%20pens%20and%20the%20following%20customers%20are%20in%20the%20following%20columns.%20I%20want%20the%20new%20table%20to%20show%20customer%20X%20with%20%2410%2C%20%240%2C%20%2412.00%20etc%20(so%20lookup%20customer%20quantity%20and%20multiply%20by%20price).%20I%20just%20need%20the%20suggestion%20on%20how%20to%20build%20this.%20Vlookups%20are%20too%20long%20-%20is%20there%20a%20command%20or%20function%20I%20should%20research%20that%20will%20help%20me%20make%20this%3F%20Thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1844119%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1844295%22%20slang%3D%22de-DE%22%3ESubject%3A%20Combining%20data%20in%20a%20new%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1844295%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F854359%22%20target%3D%22_blank%22%3E%40mariong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EA%20noble%20offer%20from%20Mr.%3C%2FSPAN%3E%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3ESergei%20Baklan%20%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Ecan%20only%20recommend%20it%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3A)))%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Flook-up-in-table-based-on-4-values%2Fm-p%2F1839794%23M79568%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Flook-up-in-table-based-on-4-values%2Fm-p%2F1839794%23M79568%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ETo%20continue%20tinkering%20yourself%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CPRE%3EXLOOKUP(%0A%20%20D14%2C%0A%20%20(%24A%242%3A%24A%249%26amp%3B%24B%242%3A%24B%249%26amp%3B%24C%242%3A%24C%249%3DA14%26amp%3BB14%26amp%3BC14)*%24D%242%3A%24D%249%2C%0A%20%20%24F%242%3A%24F%249%2C%0A%20%20%22no%20such%22%2C%0A%20%20%20-1%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EHope%20it%20helped%20you%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1844300%22%20slang%3D%22de-DE%22%3ERE%3A%20Combining%20data%20in%20a%20new%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1844300%22%20slang%3D%22de-DE%22%3EAlthough%20I%20think%20Power%20Query%20is%20more%20appropriate%20here.%20Add%20a%20MS%20Excel%20file%20(without%20sensitive%20data)%20to%20your%20project.%20Knowing%20the%20Excel%20version%20is%20also%20an%20advantage.%20So%20you%20could%20get%20a%20much%20more%20concrete%20solution%20than%20the%20one%20I%20sent.%20Thank%20you%20for%20your%20understanding%20and%20patience%20Nikolino%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E
New Contributor

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

@mariong 

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

@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

@mariong 

 

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)