MarCat

VLOOKUP in Excel for shopkeepers: check a supplier bill against your item list

Use VLOOKUP in Excel to check a supplier bill against your item list: find new items, MRP changes and cost changes in minutes. Step by step, with store examples.

By Swarbhanu Gupta, who has run Banjara supermarket in Ahmedabad since January 2025 (billing on MarCat since April 2026) · 11 November 2026

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.

  1. In E1 type "Our MRP". In E2 type: =VLOOKUP(A2, Items!$A:$D, 3, FALSE)
  2. In F1 type "Our cost". In F2 type: =VLOOKUP(A2, Items!$A:$D, 4, FALSE)
  3. 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:

  1. G "MRP changed?": =IF(C2<>E2, "MRP CHANGED", "")
  2. 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

  1. Paste the bill lines into the Bill sheet.
  2. Drag the formulas down.
  3. Filter for flags.
  4. New items: add to the item list.
  5. MRP changed: decide whether old stock stays at the old MRP, and relabel or separate it on the shelf.
  6. 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.

Questions

What does VLOOKUP do?
It takes a value, such as a barcode, finds it in the first column of another table, and brings back a value from the same row, such as the MRP or cost you have on record.
Why does VLOOKUP show #N/A?
With FALSE as the last part of the formula, #N/A means the barcode was not found in your item list. Either it is a new item, or the barcode is stored differently in the two sheets, for example as text in one and as a number in the other, or with an extra space.
Should I use TRUE or FALSE in VLOOKUP?
For barcodes and item codes, always FALSE. FALSE asks for an exact match. If you leave it out, Excel looks for an approximate match, which can quietly bring back the wrong item.
Can MarCat bring in my item list from Excel?
Yes. Items can be brought in from Excel, and from the billing software you use today. We help you move, so you do not have to type your stock in again.