Forum Discussion

Ian955's avatar
Ian955
Copper Contributor
Oct 26, 2020
Solved

Comparing a price list to an invoice

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 formul...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Oct 26, 2020

    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.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 26, 2020

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

 

Resources