Hello Guys! I need a very specific formula.

%3CLINGO-SUB%20id%3D%22lingo-sub-2720690%22%20slang%3D%22en-US%22%3EHello%20Guys!%20I%20need%20a%20very%20specific%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2720690%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20have%20a%20list%20of%20product%20that%20were%20sold%20to%20different%20customers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20thing%20is%20that%20they%20all%20take%20different%20%25%20and%20what%20I%20wanna%20do%20is%20that%20when%20I%20type%20in%20a%20H2%20the%20name%20of%20the%20company(for%20an%20example%20John)%20it%20should%20calculate%20-5%25%20of%20E2%20and%20put%20that%20number%20to%20a%20cell%20I2%2C%20but%20if%20I%20put%20in%20H2%20a%20different%20company%20(for%20an%20example%20Benny)%20it%20should%20calculate%20-10%25%20of%20E2%2C%26nbsp%3Bbut%20if%20I%20put%20in%20H2%20a%20third%20different%20company%20(for%20an%20example%20Benny)%20it%20should%20calculate%20-15%25%20of%20E2.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20advise%20if%20that%20is%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3CBR%20%2F%3EAlex%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2720690%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2720714%22%20slang%3D%22en-US%22%3ERe%3A%20Hello%20Guys!%20I%20need%20a%20very%20specific%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2720714%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1146833%22%20target%3D%22_blank%22%3E%40SunnyKavalov%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20create%20separate%20table%20with%20discount%20percents%20for%20customers%20and%20use%20any%20lookup%20function%20(e.g.%20XLOOKUP%20)%20on%20it%20to%20calculate%20actual%20discounts.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

So I have a list of product that were sold to different customers.

 

The thing is that they all take different % and what I wanna do is that when I type in a H2 the name of the company(for an example John) it should calculate -5% of E2 and put that number to a cell I2, but if I put in H2 a different company (for an example Benny) it should calculate -10% of E2, but if I put in H2 a third different company (for an example Benny) it should calculate -15% of E2. 

 

Can you please advise if that is possible?

 

Best Regards
Alex

2 Replies

@SunnyKavalov 

You may create separate table with discount percents for customers and use any lookup function (e.g. XLOOKUP ) on it to calculate actual discounts.

@SunnyKavalov 

Create a lookup table:

S0734.png

The formula in I2 is

=-VLOOKUP(H2,$A$2:$B$6,2,FALSE)*E2

This can be filled down.