Blog

Compare catalog prices in a survey results sheet

Match survey responses to catalog items, calculate price differences, and catch duplicate IDs, missing matches, and currency or unit mismatches.

Lauren HughesTools and utilities Tools 5 min read

You have catalog prices and a sheet of survey responses, but the rows may be in different orders or use slightly different product names. Comparing them row by row can lead to false mismatches.

Match each response to a catalog item using a unique ID, then calculate the difference between the reported price and the catalog price. You can inspect and sort the CSV in the CSV Editor, and use a spreadsheet formula to calculate the comparisons.

Prepare the files

  1. Open a copy of the survey file in the CSV Editor. Use it to inspect the columns, find entries, and sort the data. The editor is useful for checking and cleaning CSV rows, but use a spreadsheet formula for the price lookup and calculation.
  2. Identify a column that uniquely identifies each product, such as a product ID or SKU. Do not rely on row order, and avoid matching on product names alone if names are inconsistent or repeated.
  3. Open the catalog and survey data in a spreadsheet such as Excel or Google Sheets. Put the catalog in a sheet named Catalog, with product IDs in column A and catalog prices in column B. Put survey product IDs in column A and reported prices in column B of a sheet named Survey.
  4. Check that both price columns use the same currency and unit. A survey entry of 12 per pound cannot be compared directly with a catalog price of 12 per item, for example. Make sure prices are numbers, not text containing currency symbols or notes.
  5. Keep the original files unchanged. Work on copies so you can return to the source if an ID, price, or row needs checking.

Calculate the price difference

In the Survey sheet, add a column for the catalog price. In cell C2, enter this formula to look up the catalog price using the product ID in A2:

=IFERROR(VLOOKUP(A2,Catalog!$A:$B,2,FALSE),"Not found")

The formula looks for an exact ID match in the catalog and returns its price. If there is no match, it displays Not found rather than silently comparing the response with the wrong item.

Add another column for the difference. In D2, enter:

=IF(ISNUMBER(C2),B2-C2,"")

A positive result means the reported survey price is higher than the catalog price. A negative result means it is lower. A result of zero means the prices match. Fill the formulas down the rows to compare every response. Spreadsheet formula separators may differ with regional settings, so if a formula is rejected, check whether your spreadsheet expects semicolons instead of commas.

If you also want to see the size of each difference relative to the catalog price, add a percentage column. In E2, enter =IF(OR(NOT(ISNUMBER(C2)),C2=0),"",D2/C2), then format the results as percentages. The check for zero prevents dividing by a catalog price of zero.

Review the results before acting

  • Check missing matches. Filter or search for Not found. Look for typos, extra spaces, missing leading zeros, or an ID that does not exist in the catalog. If an ID has leading zeros, keep it as text during import so they are not removed.
  • Check repeated catalog IDs. If an ID appears more than once in the catalog, a lookup may return only one matching row. Resolve duplicate IDs or confirm which catalog entry is correct before trusting the comparison.
  • Sort the differences. Sort by the difference or percentage column to review the largest gaps first. Use the CSV Editor to sort or find records in the source data when you need to investigate a row.
  • Check unusual values against the source. A large difference might be a typing mistake, but it could also reflect a sale, a different package size, or a respondent reporting what they expect to pay rather than a price they saw.

Common reasons prices do not match

The product names look the same, but the items differ

Two entries can share a name while differing in size, model, or package count. Match on a unique product ID where possible. If the survey has no ID, create a careful mapping from each response to a catalog item and review ambiguous names manually.

The prices use different currencies or units

Confirm the currency, tax treatment, and price basis before interpreting a difference. A price before tax will not match one that includes tax, and a per-ounce price will not match a total package price without conversion. Keep the original values and record any conversion separately.

The survey has several responses for one product

Repeated product IDs can be expected when multiple people answered the survey. Each response can be compared with the same catalog price. Do not remove repeated survey rows unless you have confirmed they are accidental duplicate submissions, because that can discard valid answers.

A blank or unusually low price appears

Check whether the cell is empty, contains text, or uses zero to mean “not provided.” Do not treat a missing answer as a price of zero. Decide how missing values should be labeled before calculating differences, and keep them out of averages unless your analysis calls for a different treatment.

Save a comparison you can check later

Keep the workbook with the formulas as your working record. If you need to share a CSV, export the comparison sheet separately and check the saved file afterward. CSV files do not preserve workbook formulas or multiple sheets, so keep a copy of the workbook if you want to recalculate or audit the results later.

The CSV Editor works with files in your browser. The file you open stays on your device and is not uploaded.

More articles