SOLVED

Comparing a price list to an invoice

%3CLINGO-SUB%20id%3D%22lingo-sub-1818489%22%20slang%3D%22en-US%22%3EComparing%20a%20price%20list%20to%20an%20invoice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1818489%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20a%20price%20list%20%26amp%3B%20an%20invoice%20both%20of%20which%20are%20very%20long%2C%20the%20supplier%20is%20charging%20more%20for%20some%20items%20on%20their%20invoice%20than%20agreed%20in%20the%20price%20list%20but%20not%20all%20items%2C%20I%20would%20like%20a%20formula%20that%20I%20can%20use%20in%20column%20J%20to%20find%20out%20how%20much%20they%20are%20over%20charging%2C%20any%20help%20with%20this%20is%20much%20appreciated.%20The%20descriptions%20may%20vary%20on%20the%20invoice%20but%20the%20item%20ref's%20in%20columns%20A%20%26amp%3B%20E%20are%20the%20same%20for%20both%20the%20price%20list%20%26amp%3B%20invoice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Ian955_0-1603717002052.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F229244i9C6E41410BD400FB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Ian955_0-1603717002052.png%22%20alt%3D%22Ian955_0-1603717002052.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1818489%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-1818763%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20a%20price%20list%20to%20an%20invoice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1818763%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F846034%22%20target%3D%22_blank%22%3E%40Ian955%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20J4%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DH4-VLOOKUP(E4%2C%24A%244%3A%24C%248%2C3%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eif%20you%20want%20to%20compare%20the%20prices%2C%20or%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DG4*(H4-VLOOKUP(E4%2C%24A%244%3A%24C%248%2C3%2CFALSE))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eif%20you%20want%20to%20calculate%20the%20overcharge%20on%20the%20total.%3C%2FP%3E%0A%3CP%3EAdjust%20the%20range%2C%20then%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1818949%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20a%20price%20list%20to%20an%20invoice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1818949%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F846034%22%20target%3D%22_blank%22%3E%40Ian955%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20can%20guarantee%20that%20your%20item%20IDs%20are%20both%20reliable%20and%20are%20the%20natural%20numbers%20starting%20at%201%20you%20do%20not%20even%20need%20to%20search%20for%20the%20price%3B%20it%20is%20given%20by%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20INDEX(Prices%5BPrice%5D%2C%20%5B%40%5BItem%20Ref%5D%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMore%20generally%20you%26nbsp%3B%20will%20need%20a%20lookup%20based%20upon%20the%20product%20name%20or%20ID%20to%20give%20the%20agreed%20price%20and%20hence%20the%20overcharge%2C%20In%20the%20latest%20versions%20of%20Excel%20365%20the%20formula%20for%20the%20total%20overcharge%20could%20be%20expressed%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20priceCharged%2C%20Invoice%5BPrice%5D%2C%0A%20%20quanitiy%2C%20Invoice%5BQuantity%5D%2C%0A%20%20agreedPrice%2C%20XLOOKUP(Invoice%5BDescription%5D%2C%20Prices%5BDescription%5D%2C%20Prices%5BPrice%5D)%2C%0A%20%20overcharge%2C%20(priceCharged%20-%20agreedPrice)%20*%20quanitiy%2C%0A%20%20SUM(overcharge)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1822274%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20a%20price%20list%20to%20an%20invoice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1822274%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThanks%20for%20your%20help%20Hans%2C%20you%20have%20answered%20my%20question%20perfectly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1822276%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20a%20price%20list%20to%20an%20invoice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1822276%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3BThanks%20for%20your%20help%20Peter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1822916%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20a%20price%20list%20to%20an%20invoice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1822916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BHi%20Hans%2C%20this%20worked%20perfectly%20on%20the%20exercise%2C%20I'm%20trying%20to%20use%20it%20in%20my%20actual%20spreadsheet%2C%20what%20does%20the%203%20before%20the%20word%20false%20relate%20to%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EIan%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi,

I have a price list & an invoice both of which are very long, the supplier is charging more for some items on their invoice than agreed in the price list but not all items, I would like a formula that I can use in column J to find out how much they are over charging, any help with this is much appreciated. The descriptions may vary on the invoice but the item ref's in columns A & E are the same for both the price list & invoice.

 

Ian955_0-1603717002052.png

 

7 Replies
Highlighted
Best Response confirmed by Ian955 (Occasional Contributor)
Solution

@Ian955 

In J4:

=H4-VLOOKUP(E4,$A$4:$C$8,3,FALSE)

if you want to compare the prices, or

=G4*(H4-VLOOKUP(E4,$A$4:$C$8,3,FALSE))

if you want to calculate the overcharge on the total.

Adjust the range, then fill down.

Highlighted

@Ian955 

If you can guarantee that your item IDs are both reliable and are the natural numbers starting at 1 you do not even need to search for the price; it is given by

= INDEX(Prices[Price], [@[Item Ref]])

More generally you  will need a lookup based upon the product name or ID to give the agreed price and hence the overcharge, In the latest versions of Excel 365 the formula for the total overcharge could be expressed

= LET(
  priceCharged, Invoice[Price],
  quanitiy, Invoice[Quantity],
  agreedPrice, XLOOKUP(Invoice[Description], Prices[Description], Prices[Price]),
  overcharge, (priceCharged - agreedPrice) * quanitiy,
  SUM(overcharge) )

 

Highlighted

@Hans Vogelaar Thanks for your help Hans, you have answered my question perfectly.

Highlighted

@Peter Bartholomew Thanks for your help Peter

Highlighted

@Hans Vogelaar Hi Hans, this worked perfectly on the exercise, I'm trying to use it in my actual spreadsheet, what does the 3 before the word false relate to?

Thanks

Ian

Highlighted

@Ian955 

In VLOOKUP(E4,$A$4:$C$8,3,FALSE):

E4 is the cell whose value you want to search for.

$A$4:$C$8 is the lookup range. VLOOKUP searches its first column $A$4:$A$8 for the value of E4.

3 is the number of the column to return a result from - in this example column C.

FALSE means that Excel looks for an exact match.

Highlighted

@Hans Vogelaar  Thanks very much for your help Hans