Skip to main content

How to Use VLOOKUP to Assign Data

Learn how to automatically assign data from supplier lists to your products using Excel's VLOOKUP function.

When to Use This​

Use VLOOKUP when you need to:

  • Import prices from supplier updates
  • Match data between two spreadsheets
  • Assign values based on SKU, EAN, or Model
  • Update product information from external sources

Understanding VLOOKUP​

VLOOKUP searches the first column of a table and returns a value from the same row in a different column.

Syntax​

=VLOOKUP(search_criterion; matrix; column_index; range_reference)

Parameters​

  • search_criterion: The value to search for (e.g., SKU or EAN)
  • matrix: The table range to search in
  • column_index: Which column number to return data from
  • range_reference: FALSE for exact match, TRUE for approximate match

Step-by-Step: Assign Prices from Supplier List​

This example shows how to update product prices from a supplier's Excel file.

Step 1: Open Supplier List​

Open the Excel file from your supplier that contains the updated prices.

Step 2: Sort the Supplier List​

VLOOKUP requires the data to be sorted by the first column.

  1. Select the first row of the supplier data
  2. Go to the Data ribbon
  3. Click Sort from A to Z

Step 3: Load Products in cobby​

Switch to cobby and click "Load products" to load your product data.

Step 4: Enter VLOOKUP Formula​

  1. Click on the Price column (or whichever column you want to update)
  2. Set the cell format to Standard (right-click > Format Cells > Standard)
  3. In the formula bar, start typing the VLOOKUP formula:
=VLOOKUP([@Model];

The search criterion is the Model column (or EAN, depending on your supplier data).

Step 5: Select the Supplier Data Range​

  1. Switch to the supplier list worksheet
  2. Select the entire data range including headers
  3. This becomes your matrix parameter

Step 6: Complete the Formula​

Add the column index and range reference:

=VLOOKUP([@Model];SupplierSheet!$B$3:$D$7;3;FALSE)
  • 3: The price is in the 3rd column of the selected range
  • FALSE: We want an exact match

Step 7: Make Matrix Reference Absolute​

Add dollar signs ($) before each cell reference in the matrix to make it absolute:

=VLOOKUP([@Model];SupplierSheet!$B$3:$D$7;3;FALSE)

This ensures the range doesn't change when you copy the formula down.

Step 8: Apply to All Products​

  1. Copy the cell with the formula
  2. Select the entire column
  3. Paste the formula
  4. Or double-click the small square at the bottom-right of the cell

Step 9: Save Products​

Click "Save products" in cobby to transfer the updated prices to your store.

Common Use Cases​

Match by EAN​

=VLOOKUP([@EAN];SupplierData!$A$2:$D$100;2;FALSE)

Import Multiple Columns​

Create separate VLOOKUP formulas for each column:

=VLOOKUP([@SKU];SupplierData!$A$2:$F$100;3;FALSE)  ' Price
=VLOOKUP([@SKU];SupplierData!$A$2:$F$100;4;FALSE)  ' Stock
=VLOOKUP([@SKU];SupplierData!$A$2:$F$100;5;FALSE)  ' Description

Handle Missing Matches​

Wrap in IFERROR to handle products not in supplier list:

=IFERROR(VLOOKUP([@Model];SupplierData!$A$2:$D$100;3;FALSE);"")

Tips​

  • Always use FALSE for range_reference to find exact matches
  • Sort your data by the first column of the matrix
  • Use absolute references ($ signs) for the matrix range
  • Check column numbers carefully (1 = first column, 2 = second, etc.)
  • Test with one cell before applying to all products

Troubleshooting​

Getting #N/A error?

  • The search value wasn't found in the first column
  • Check that your search criterion matches exactly (no extra spaces)
  • Verify the supplier list is sorted correctly

Getting #REF! error?

  • The column_index is larger than the number of columns in your matrix
  • Check that you're referencing the correct column number

Wrong values returned?

  • The matrix may not be sorted correctly
  • The column_index may be wrong
  • Check that you're using FALSE for exact match

Formula not calculating?

  • Set the cell format to "Standard" instead of "Text"
  • Right-click > Format Cells > Category: Standard

Advanced: Use INDEX-MATCH Instead​

For more flexibility, consider using INDEX-MATCH instead of VLOOKUP (coming soon). INDEX-MATCH doesn't require sorted data and can look in any column direction.