Data Import & Export

Match Inventory Barcodes and Return Product Descriptions in Excel

Amos GikundaAmos Gikunda Nov 18, 2026 869 views

Question details

Match products from two inventory systems using their shared barcodes and return their descriptions.

Match Inventory Barcodes and Return Product Descriptions in Excel
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
Two inventory tables use different stock codes but share a barcode for each common product.
Observed behavior
A matched product list should link the tables by barcode and display the relevant descriptions.
Before you start

Make sure both inventory tables have a Barcode column and that the table containing descriptions has a clearly labeled description column. Store barcodes as text in both sources so leading zeros are preserved.

Solution 1Recommended

Merge the inventory tables by barcode in Excel Power Query

Use a Power Query merge to create a refreshable list of products found in both inventory tables.

1
Import the first table

Select a cell in the first inventory table and choose Data > From Table/Range. Confirm that the Barcode column has the Text data type, then use Home > Close & Load To to create a connection.

2
Import the second table

Select the second inventory table and choose Data > From Table/Range. Set its Barcode column to Text and use Home > Close & Load To to create a connection.

3
Merge on barcode

Choose Data > Get Data > Combine Queries > Merge. Select the two inventory queries, click the Barcode column in each, and choose Inner join to keep only matching rows. Select OK.

4
Expand descriptions

In the merged query, select the expand icon in the new table column. Choose the description and any stock-code fields you want to include, then select OK.

5
Load and refresh the result

Choose Home > Close & Load to place the matched list in Excel. After updating either source table, select Data > Refresh All to update the result.

Merge the inventory tables by barcode in Excel Power Query
Check duplicate barcodes: If a barcode appears more than once in either source, the merge can return multiple rows for that barcode.
Match inventory in WPS Spreadsheets

Return matching descriptions with WPS Spreadsheets

Use VLOOKUP in WPS Spreadsheets to find each barcode in a second inventory list and return its product description. Filter the results to display common products.

  1. 1. Arrange the inventory lists: Open the workbook in WPS Spreadsheets. Put the first list's barcodes in Sheet1 column A, and put the second list's barcodes and descriptions in Sheet2 columns A and B.
  2. 2. Check barcode formatting: Select the barcode columns on both sheets and set their cell format to Text. Check that the stored values still contain any leading zeros.
  3. 3. Enter the lookup formula: In Sheet1 cell B2, enter =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$1000,2,FALSE),"Not found"). Adjust the row limit to cover the second list.
  4. 4. Fill and filter the results: Drag the fill handle from B2 down alongside the first list. Use Data > Filter on column B and exclude Not found to show matching products.
Match inventory records using shared barcodesReturn product descriptions with a familiar spreadsheet formulaOpen and save Microsoft Excel XLSX filesUse WPS Office for free with a familiar spreadsheet interface
QA img-9

Frequently Asked Questions

How do I update the matched list after inventory changes?

Update the source tables, then choose Data > Refresh All in Excel to rerun the Power Query merge.

Why are some barcodes not matching?

Check that both Barcode columns use the same text values, including leading zeros and spaces. In Power Query, select each Barcode column and set its data type to Text.

Can I show products that have no match in the second table?

Yes. In the Power Query Merge dialog, choose a Left Outer join instead of Inner to keep all rows from the first table, including unmatched products.

What happens if the second inventory list contains duplicate barcodes?

A Power Query merge can return multiple matching rows. A VLOOKUP formula returns the first match, so check the source list for duplicates if each barcode should identify one product.