A distributor sends a bill with 40 lines. Some are items you already stock. A few are new. Two or three have a new MRP or a new rate. Checking each line by eye against your item list takes half an hour and you still miss one.
VLOOKUP does this check in a few minutes. You do not need to be good at Excel. You need two sheets and one formula.
What VLOOKUP does, in shop words
VLOOKUP takes one value, finds it in a list, and brings back something from the same row.
In a store, that means: take the barcode from the supplier bill, find it in your item list, and bring back the MRP or cost you have on record. Then you compare the two.
The formula has four parts:
=VLOOKUP(what to find, where to look, which column to bring back, FALSE)
| Part | In a store | Example |
|---|---|---|
| What to find | The barcode on the bill line | A2 |
| Where to look | Your item list, barcode in the first column | Items!$A:$D |
| Which column | Count from the barcode column: 1, 2, 3... | 3 for MRP |
| FALSE | Exact match only | always FALSE for barcodes |
Step 1: Set up your two sheets
Sheet "Items" is your item list. The barcode must be the first column. Example with made-up numbers:
| A: Barcode | B: Item | C: MRP (₹) | D: Cost (₹) |
|---|---|---|---|
| 8901234567890 | Glucose biscuit 200 g | 30 | 25.50 |
| 8901234567891 | Toor dal 1 kg | 160 | 138 |
| 8901234567892 | Bath soap 100 g | 45 | 37 |
Sheet "Bill" is the supplier bill, typed in or copied from the PDF or Excel the supplier sends:
| A: Barcode | B: Item on bill | C: Bill MRP (₹) | D: Bill rate (₹) |
|---|---|---|---|
| 8901234567890 | Glucose biscuit 200 g | 30 | 26 |
| 8901234567891 | Toor dal 1 kg | 170 | 146 |
| 8901234567899 | Hand wash 200 ml | 99 | 79 |
Step 2: Bring your MRP and cost next to each bill line
In the Bill sheet, add two columns.
- In E1 type "Our MRP". In E2 type:
=VLOOKUP(A2, Items!$A:$D, 3, FALSE) - In F1 type "Our cost". In F2 type:
=VLOOKUP(A2, Items!$A:$D, 4, FALSE) - Select E2 and F2 and drag them down to the last bill line.
The $ signs keep the item list fixed while you drag. Without them, the range slides down one row at a time and lines near the bottom stop finding their match.
Step 3: Flag the lines that need attention
Add two more columns:
- G "MRP changed?":
=IF(C2<>E2, "MRP CHANGED", "") - H "Rate changed?":
=IF(D2<>F2, "RATE " & TEXT(D2-F2, "0.00"), "")
Now the sheet tells you, line by line:
| Barcode | Item | Bill MRP | Our MRP | Flag |
|---|---|---|---|---|
| 8901234567890 | Glucose biscuit 200 g | 30 | 30 | RATE 0.50 |
| 8901234567891 | Toor dal 1 kg | 170 | 160 | MRP CHANGED, RATE 8.00 |
| 8901234567899 | Hand wash 200 ml | 99 | #N/A | not in item list (see Step 4) |
Use the Filter button on the header row and show only flagged lines. That is your list for today. A line where "Our MRP" shows #N/A will show #N/A in the flag columns too; Step 4 deals with those.
Step 4: Handle #N/A properly
#N/A means the barcode was not found in your item list. It is useful information, not an error to hide. It usually means one of three things:
- It is a new item. Add it to your item list before the stock goes on the shelf.
- The barcode is stored differently. One sheet has it as text, the other as a number. Or there is a hidden space at the end.
- The supplier used their own code, not the barcode on the pack.
To show a clear word instead of #N/A, wrap the formula:
=IFERROR(VLOOKUP(A2, Items!$A:$D, 3, FALSE), "NEW ITEM")
Do this only after you understand why lines are missing. If you wrap too early, a format problem looks like 30 new items.
The barcode traps that break VLOOKUP
Most VLOOKUP trouble in a shop comes from barcodes, not from the formula.
| Problem | What you see | Fix |
|---|---|---|
| 13-digit barcode shows as 8.90123E+12 | Hard to read and check by eye | Select the column, Format Cells, Number with 0 decimals |
| Barcode is text in one sheet, number in the other | #N/A on items you know exist | Make both columns the same format, then re-enter or use =TRIM(A2) / =VALUE(A2) in a helper column |
| Extra space after the barcode | #N/A | =TRIM(A2) in a helper column, look up that instead |
| Leading zero dropped | #N/A on some short codes | Store codes as text in both sheets |
| Same barcode twice in item list | VLOOKUP returns only the first one | Clean up duplicates first |
The last one matters in a supermarket. If you keep two rows for the same barcode because the MRP changed, VLOOKUP brings back only the first row it finds. Your check will compare against the older MRP without telling you.
Common mistakes with the formula
- Leaving out FALSE. Without it, Excel does an approximate match and can bring back a nearby barcode's price. For codes, always FALSE.
- Barcode not in the first column of the range. VLOOKUP only looks in the first column of "where to look". If your item list starts with item name, change the range to start at the barcode column.
- Counting columns from A, not from the start of the range. If the range is
Items!$B:$E, column 1 is B. - Matching by item name. "Toor Dal 1kg" and "Toor dal 1 kg" are different to Excel. Use barcodes or item codes wherever you can.
If you have Microsoft 365 or Excel 2021, XLOOKUP does the same job and can look in any column, so the barcode does not have to come first:
=XLOOKUP(A2, Items!$A:$A, Items!$C:$C, "NEW ITEM")
The fourth part is what to show when the barcode is not found. VLOOKUP also works in older versions of Excel, which many shop PCs still run, so it is the one to learn first.
A 10-minute routine for each supplier bill
- Paste the bill lines into the Bill sheet.
- Drag the formulas down.
- Filter for flags.
- New items: add to the item list.
- MRP changed: decide whether old stock stays at the old MRP, and relabel or separate it on the shelf.
- Rate changed: check against the scheme or rate the salesman promised before you pay.
From our counter
The sheet is only as good as the item list behind it. In any running supermarket the list drifts: a new pack size comes in under an old name, a cashier adds a quick item with no barcode during the evening rush, an MRP gets changed on the shelf label but not in the list. Every one of those turns into a false flag or a missed one at bill-check time. Our habit is simple: when the bill check throws up a mismatch, fix the item list first, then the bill. A clean list makes tomorrow's check faster.
Where software helps
VLOOKUP is a good check when your item list lives in Excel. It starts to strain when there are many suppliers a day, more than one MRP for the same barcode, and stock that has to move with every bill. At that point the check works better inside the purchase entry itself, so the comparison happens while the bill is being entered and not in a separate sheet.
How MarCat handles it
We have run Banjara, our own supermarket in Ahmedabad, since January 2025, and it has billed on MarCat since April 2026. Checking supplier bills against your own prices is a daily job in any store. This is what MarCat does:
- Purchase entry the way bills arrive. Purchase entry follows how a supplier bill actually comes in, with vendor records and outstanding balances in the same system.
- MRP changes are asked about, not missed. Every MRP change at purchase entry asks: keep both prices or replace. Keep both, and old stock stays sellable at the old MRP while new stock goes in at the new MRP, each with its own stock.
- Your Excel list comes with you. Items can be brought in from the software you use today or from Excel, and the store owns its data and can export it whenever it wants.
If you are checking supplier bills in Excel today and want to see how MarCat handles purchase entry, message us on WhatsApp at +91 96787 65518.
