Excel Formulas to Lookup Product Price by SKU

Managing massive retail inventory lists often leads to frustrating pricing mismatches and manual data entry exhaustion. Before upgrading to costly software or seeking standard funding sources for corporate IT overhauls, managers should optimize their existing spreadsheets. Utilizing a precise Excel lookup formula grants teams immediate, automated pricing accuracy. However, one key stipulation is that your SKU database must maintain unique identifiers to prevent duplicate mismatches. For instance, applying =XLOOKUP("SKU-102", A2:A100, C2:C100) instantly retrieves the exact price for SKU-102. Below, we outline step-by-step methods to implement these formulas effectively.

In retail, e-commerce, and warehouse management, the Stock Keeping Unit (SKU) is the universal language of inventory. An SKU is a unique alphanumeric code assigned to a specific product to identify its price, manufacturer, size, color, and other details. One of the most common daily tasks for inventory analysts, store managers, and financial teams is looking up a product's price using its SKU.

Excel offers several powerful lookup formulas to automate this process. Whether you are running a legacy version of Excel or using the cutting-edge Microsoft 365, this guide will walk you through the best methods to retrieve product prices using SKUs. We will cover the classic VLOOKUP, the flexible INDEX & MATCH combination, and the modern, robust XLOOKUP.

The Sample Dataset

To understand how these formulas work, let us establish a standard sample dataset. Imagine we have an inventory sheet containing the following table in the range A1:E6:

Our objective is simple: enter an SKU into a search cell (e.g., cell G2) and display the corresponding product price in cell H2.

Method 1: The Traditional VLOOKUP Formula

The VLOOKUP (Vertical Lookup) function is the most famous lookup tool in Excel history. It searches for a specified value in the first column of a table and returns a value in the same row from a specified column to the right.

The VLOOKUP Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Step-by-Step Implementation

To look up the price of SKU-1003 using our sample data:

1. Place your target SKU ("SKU-1003") in cell G2.
2. In cell H2, enter the following formula:
   

   =VLOOKUP(G2, A2:E6, 4, FALSE)

3. Press Enter. Cell H2 will return $199.99.

How It Works

• G2 (lookup_value): This is the value we are searching for ("SKU-1003").
• A2:E6 (table_array): This is the entire data range where Excel will search.
• 4 (col_index_num): Since our Price column is the fourth column in our selected range (Column A is 1, B is 2, C is 3, D is 4), we enter 4.
• FALSE (range_lookup): This forces Excel to find an exact match. If the SKU is not found, it returns an error instead of a wrong price. Always use FALSE or 0 for SKU lookups!

Limitations of VLOOKUP

While reliable, VLOOKUP has two main weaknesses: it can only search from left to right (the SKU column must always be the leftmost column in your data range), and inserting or deleting columns inside your table can break the formula because the hardcoded column index number (e.g., 4) does not adjust automatically.

Method 2: The Modern and Powerful XLOOKUP Formula

If you are using Microsoft 365, Office 2021, or Excel for the Web, XLOOKUP is the ultimate lookup tool. It was specifically designed to replace VLOOKUP by fixing all of its inherent limitations.

The XLOOKUP Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Step-by-Step Implementation

Using the same dataset, to look up the price of the SKU in cell G2:

1. Select cell H2 and enter the following formula:
   

   =XLOOKUP(G2, A2:A6, D2:D6)

2. Press Enter. Cell H2 will output $199.99.

Why XLOOKUP is Superior

• No Left-to-Right Limitation: The SKU column does not need to be on the left. Even if Column A contained Prices and Column D contained SKUs, XLOOKUP would work perfectly.
• No Hardcoded Column Indexes: Because we reference exact ranges (e.g., D2:D6), adding or deleting columns in our sheet will never break the formula.
• Defaults to Exact Match: You do not need to type "FALSE" at the end; exact matching is XLOOKUP's default behavior.
• Built-in Error Handling: You can include custom messages if an SKU is missing. For example: =XLOOKUP(G2, A2:A6, D2:D6, "SKU Not Found").

Method 3: The Highly Flexible INDEX & MATCH Combination

For large databases or older versions of Excel (like Excel 2010, 2013, or 2016) that do not support XLOOKUP, the combination of INDEX and MATCH is the professional standard.

The Syntax Explained

This method nests the MATCH function (which finds the row number of a value) inside the INDEX function (which retrieves a value from a specific row and column range).

=INDEX(return_column, MATCH(lookup_value, lookup_column, 0))

Step-by-Step Implementation

To lookup the price using INDEX and MATCH in our sheet:

1. In cell H2, enter the following formula:
   

   =INDEX(D2:D6, MATCH(G2, A2:A6, 0))

2. Press Enter. The cell will output $199.99.

How It Works

• MATCH(G2, A2:A6, 0): Searches the SKU column (A2:A6) for the SKU in G2 ("SKU-1003"). The 0 specifies an exact match. It finds the SKU in the 3rd row of that range and outputs 3.
• INDEX(D2:D6, 3): Looks at the price column (D2:D6) and retrieves the value from the 3rd row, which is $199.99.

Like XLOOKUP, this formula does not care if the price column is to the left or right of the SKU column, and it does not break when columns are inserted or deleted.

Handling Common Errors in SKU Lookups

When working with large datasets, lookup formulas will occasionally fail. Understanding how to troubleshoot these failures is essential.

1. The dreaded #N/A Error

This means Excel could not find the lookup SKU. To prevent ugly error codes on your dashboard, wrap your formulas in the IFERROR function to display a clean warning:

=IFERROR(VLOOKUP(G2, A2:E6, 4, FALSE), "SKU Not Found")

2. Text vs. Number Mismatches

Sometimes, SKUs consist entirely of numbers (e.g., 1001034). If your lookup cell is formatted as "Text" but your main database table stores those SKUs as "Numbers" (or vice versa), Excel will fail to find a match. Ensure both the source lookup table and search cells have identical data formatting.

3. Leading and Trailing Spaces

An SKU that looks like "SKU-1001" might actually be stored as "SKU-1001 " (with a trailing space). To fix this database-wide issue, use the TRIM function to clean up any unwanted spaces before searching.

Summary: Which Formula Should You Use?

• Use XLOOKUP if you and your team are on Microsoft 365 or modern Excel versions. It is faster, less prone to errors, and simpler to write.
• Use INDEX & MATCH if you need a flexible lookup that works seamlessly across all legacy versions of Excel, especially on larger enterprise databases.
• Use VLOOKUP if you are editing basic templates that already utilize it, or if you are training beginners who are just starting to grasp lookup concepts.