Forum Discussion

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

HansVogelaar

MVP

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.

Ian955
Copper Contributor
Oct 27, 2020
HansVogelaar 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
- HansVogelaar
  MVP
  Oct 27, 2020
  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.
  - Ian955
    Copper Contributor
    Oct 28, 2020
    HansVogelaar Thanks very much for your help Hans
Ian955
Copper Contributor
Oct 27, 2020
HansVogelaar Thanks for your help Hans, you have answered my question perfectly.