Function Problems

How to Find a Barcode and Return the Matching Row in Excel

Chanuka GeekiyanageChanuka Geekiyanage Nov 11, 2026 870 views

Question details

The user needs to use a scanned barcode as a lookup value to extract a corresponding row of data from another worksheet without using VBA.

How to Find a Barcode and Return the Matching Row in Excel
Product
Excel
Device & OS
not provided
Scenario
Inventory management or data lookup using barcode scanners.
Observed behavior
The user wants a dynamic formula-based approach to retrieve a matching inventory record based on a scanned barcode input, avoiding complex macro scripts.
Before you start

Verify that the barcode column in your source data is formatted as Text to prevent Excel from automatically removing leading zeros when processing the scanned input.

Solution 1Recommended

Use the FILTER Function to Return Matching Rows

The FILTER function is the most efficient way to dynamically extract entire rows of data based on a scanned barcode value without needing VBA.

By placing your barcode scan into a specific cell, the FILTER formula can instantly read that value and output the corresponding row from your master data sheet. This method spills the entire row of data automatically.

1
Select the Input Cell

Choose a specific cell (e.g., A1 on Sheet2) where you will point your barcode scanner to input the scanned code.

2
Format as Text

Select both your input cell on Sheet2 and the barcode column in your source data on Sheet1. Right-click, select 'Format Cells', and choose 'Text' to ensure leading zeros are preserved.

3
Apply the FILTER Formula

In the cell where you want the matching record to begin appearing, enter the formula: =FILTER(Sheet1!A2:E100, Sheet1!A2:A100=A1, "Not Found"). Adjust the ranges to match your actual data.

4
Scan the Barcode

Click on your input cell (A1) to make it active, then scan your barcode. The matching row from Sheet1 will automatically populate in your result area.

Use the FILTER Function to Return Matching Rows
Dynamic Updates: The FILTER function updates instantly. If multiple rows share the same barcode, all matching rows will be returned seamlessly.
Powerful Spreadsheet Tool

Manage Barcode Inventory Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like FILTER and XLOOKUP, making it incredibly easy to build dynamic barcode lookup systems without writing complex VBA code.

  1. 1. Open your Data: Launch WPS Spreadsheet and open your master inventory workbook.
  2. 2. Set Up Input Cell: Designate a dedicated cell for your barcode scanner input and format it as Text via the Format Cells menu.
  3. 3. Enter the Formula: Use the =FILTER() or =XLOOKUP() function to link your scanner input cell directly to the master inventory list.
Fully compatible with Microsoft Excel formulas like FILTER, XLOOKUP, and VLOOKUPEasily process text formatting to preserve barcode leading zerosFree and lightweight spreadsheet softwareUser-friendly interface for streamlining inventory management
microsoft office alternative - wps office

Frequently Asked Questions

Why are the leading zeros disappearing when I scan my barcode?

By default, spreadsheets treat numbers with leading zeros as numeric values and remove the zeros. To fix this, select the column where you scan barcodes, right-click, go to 'Format Cells', and change the format from 'General' to 'Text' before scanning.

Can I use VLOOKUP instead of FILTER for barcode scanning?

Yes, VLOOKUP works perfectly if you only need to return a single matching row. However, VLOOKUP requires writing a separate formula for each column you want to return, whereas FILTER or XLOOKUP can spill the entire row with just one formula.

How do I make the scanner automatically move to the next cell?

Most barcode scanners act as an external keyboard and can be programmed to add an 'Enter' or 'Tab' keystroke after each scan. Check your scanner's user manual and scan the specific configuration barcode to enable a 'Carriage Return' (Enter) suffix.

When is VBA actually necessary for barcode scanning?

VBA is required only if you need the scanned data to trigger complex actions, such as physically copying and pasting static data from one sheet to another, keeping a running timestamp log of every scan, or automatically clearing the input cell after a successful scan.