How to Do VLOOKUP in Excel: Beginner Tutorial With Examples

This tutorial walks through doing a VLOOKUP in Excel from scratch using a single concrete example: looking up product prices from a price list. By the end of the tutorial, you should be able to write a working VLOOKUP formula on your own data and understand exactly what each part of the formula does. The whole tutorial takes about 15 minutes if you follow along with your own Excel workbook open. We use the modern function name VLOOKUP throughout; it works in every version of Excel from 2007 onward.

The scenario: You run a small business with a price list of 10 products in a master sheet. A customer places an order with a list of product codes; you need to add the price for each product code to produce the order total. Instead of looking up each price manually, you can use VLOOKUP to find each price automatically. The same pattern works for thousands of products and orders, but starting with 10 products keeps the tutorial manageable to type along with.

The setup uses two areas in the same Excel sheet. The price list lives in cells A1:B11 with headers "Product Code" in A1 and "Price" in B1, then 10 products with codes like PROD-001, PROD-002 and prices like $12.50, $24.99. The order lives in cells D1:F6 with headers "Product Code" in D1, "Quantity" in E1, "Price" in F1, then 5 order lines with product codes the customer ordered. We will write a VLOOKUP in column F that pulls the price for each ordered product code.

This tutorial walks through the setup step by step, the formula construction one argument at a time, the result verification, copying the formula down with absolute references, troubleshooting common errors and expanding the same pattern to additional lookups. Each step builds on the previous one with no skipped explanations. After completing the tutorial, you will have written and used a working VLOOKUP formula and have the foundation to apply the same pattern to any business situation.

Step 1: open a fresh Excel workbook. In cell A1, type "Product Code" and press Tab. In cell B1, type "Price" and press Enter. These are the headers for the price list. In A2 type PROD-001 and Tab; in B2 type 12.50 and Enter. Repeat for nine more rows: PROD-002 with price 24.99, PROD-003 with price 5.75, PROD-004 with price 18.00, and so on through PROD-010 with whatever prices you choose. The exact prices do not matter; pick any reasonable retail values for the tutorial.

Step 2: set up the order area. In cell D1 type "Product Code" and Tab. In E1 type "Quantity" and Tab. In F1 type "Price" and Enter. In D2 type PROD-002 and Tab; in E2 type 3 and Enter. The customer ordered three units of PROD-002. Add four more order lines in D3 through D6 with various product codes from your price list and any quantities. Leave column F (Price) empty for now — that is where the VLOOKUP will go. Your sheet should now have a price list on the left and an order on the right.

Step 3: click cell F2 (the empty Price cell on the first order line). Type an equals sign to start the formula. Excel highlights F2 and shows the formula in the formula bar above the sheet. Type V-L-O-O-K-U-P and Excel autocompletes the function name. Press Tab or open parenthesis ( to start the function arguments. The formula bar now shows =VLOOKUP( with a tooltip below showing the four arguments: lookup_value, table_array, col_index_num, and the optional range_lookup.

Step 4: provide the first argument, the lookup_value. This is the value you want to find in the price list — in this case, the product code from D2. Click cell D2. Excel inserts D2 into the formula. The formula bar now shows =VLOOKUP(D2 with a tooltip showing the next argument is table_array. Type a comma to move to the next argument. The formula now shows =VLOOKUP(D2, with the cursor positioned for the table_array argument.

Step 5: provide the second argument, the table_array. This is the price list range — A2:B11. You can drag-select the range or type it manually. The easiest method: with your formula at =VLOOKUP(D2,, click cell A2 and drag to B11. Excel inserts A2:B11 into the formula. The formula now shows =VLOOKUP(D2,A2:B11. But wait — when we copy this formula down, the table_array will shift and break. We need absolute references. Press F4 once. Excel converts A2:B11 to $A$2:$B$11 with dollar signs locking the range.

Step 6: type a comma to move to the third argument, col_index_num. This is the column number within the table_array that contains the value we want to return. Our table_array has two columns — column A (Product Code) and column B (Price). We want the price, which is column 2 within the table_array. Type 2 directly. The formula now shows =VLOOKUP(D2,$A$2:$B$11,2 with the cursor ready for the next argument.

Step 7: type a comma to move to the fourth argument, range_lookup. This is the most important argument and the source of most VLOOKUP errors. Type FALSE explicitly. FALSE tells Excel to do an exact match — only return a value when the lookup_value is found exactly in the table. The alternative TRUE does approximate match which requires sorted data and silently returns wrong values otherwise. Always FALSE for business lookups. The formula now shows =VLOOKUP(D2,$A$2:$B$11,2,FALSE.

Step 8: type a closing parenthesis ) to complete the formula. The formula bar now shows =VLOOKUP(D2,$A$2:$B$11,2,FALSE). Press Enter. Excel evaluates the formula and shows the result in cell F2 — the price corresponding to the product code in D2. If you ordered PROD-002 and the price list has $24.99 for PROD-002, cell F2 now shows 24.99. The VLOOKUP found the lookup_value in the price list and returned the corresponding price.

Step 9: copy the formula down to the rest of the order lines. Click cell F2 to select it. Hover over the small green square at the bottom-right corner of the cell — this is the fill handle. Double-click the fill handle. Excel fills F3 through F6 with the same formula, automatically adjusting D2 to D3 in row 3, D4 in row 4 and so on. The price list reference $A$2:$B$11 stays locked. Each row now shows the price for that row's product code.

Step 10: verify the results. Each cell in F should show the correct price for the product code in the corresponding D cell. Click any cell in F to confirm the formula in the formula bar — F3 should show =VLOOKUP(D3,$A$2:$B$11,2,FALSE). The relative D reference adjusts each row; the absolute price list reference stays locked. If any cell shows #N/A, the corresponding D cell has a product code that is not in the price list — typically a typo or product code missing from the list.

Step 11: extend the worksheet with calculations. In cell G1 type "Total" and Enter. In G2 type =E2*F2 and Enter — this multiplies quantity by price for the line total. Copy G2 down to G3 through G6 by double-clicking the fill handle. Now each line shows the line total. In G7 type =SUM(G2:G6) — the order total combining all line totals. The full mini-application now looks up prices from the master price list and computes order totals automatically.

Step 12: try changing values to see the formula behavior. Change the quantity in E2 from 3 to 5. Cell G2 updates immediately to show the new line total, and G7 updates to show the new order total. Change the price in B2 (the price for PROD-001 in the price list). Any order line that uses PROD-001 updates immediately because VLOOKUP recalculates whenever the source data changes. The formulas are dynamic — they always reflect the current values in the source cells.

The formula behavior with absolute and relative references deserves understanding because it is what makes the formula copyable. The lookup_value reference D2 is relative — it has no dollar signs. When you copy the formula from F2 to F3, Excel automatically adjusts the reference to D3. Copy to F4 and it adjusts to D4. The relative behavior is exactly what we want for the lookup_value because each row should look up its own product code.

The table_array reference $A$2:$B$11 is absolute — both dimensions are locked with dollar signs. When you copy the formula from F2 to F3, the reference stays $A$2:$B$11 unchanged. The locked behavior is exactly what we want for the table_array because every row should look in the same price list, not a shifted version. Without the dollar signs, F3 would show =VLOOKUP(D3,A3:B12,2,FALSE) — a shifted price list missing the first row and including a row that does not exist.

The col_index_num and range_lookup arguments are constants. The number 2 stays 2 across all rows. The FALSE stays FALSE. These do not need any special formatting because they are not cell references that could shift. Some advanced users replace col_index_num with MATCH() to make the formula survive column inserts, but for tutorial purposes hardcoding 2 is simpler and works fine.

For users wondering when to use absolute versus relative references, the rule is simple: lock anything that should stay the same as you copy the formula and leave anything that should adjust as relative. The lookup_value adjusts (each row looks up a different product code) so it stays relative. The table_array stays the same (every row looks in the same price list) so it gets dollar signs. This pattern repeats across nearly every VLOOKUP in business spreadsheets.

For applying this tutorial to your own data, the steps adapt directly. Set up your reference data (product list, employee list, customer list, lookup table) in some range of cells. Set up the data needing lookups in another area. Write the VLOOKUP formula with the lookup column reference, the absolute reference to the lookup table, the column index of the value you want to return, and FALSE for exact match. Copy the formula across all rows that need lookups. The pattern works identically regardless of what kind of data you are looking up.

For employee names from employee IDs, the table_array would be your employee directory with IDs in the first column and names in the second. The col_index_num would be 2 for the name. The lookup_value would be the employee ID you want to look up. =VLOOKUP(A2,$F$2:$G$100,2,FALSE) where A is the column with employee IDs you have, F:G is the directory range, and 2 is the column index of the names. Same pattern, different data.

For shipping costs from ZIP codes, the table_array would be your shipping rate table with ZIP codes (or ZIP code ranges) in the first column and rates in subsequent columns. The col_index_num would point at the relevant rate column. The lookup_value would be the ZIP from the order. The pattern works for any reference data with a unique key in the first column and values to retrieve in subsequent columns.

For multiple values per row (looking up a product code and returning name, price, weight and category from the same row), use multiple VLOOKUP formulas with different col_index_num values. =VLOOKUP(A2,$D$2:$H$100,2,FALSE) returns the second column (name). =VLOOKUP(A2,$D$2:$H$100,3,FALSE) returns the third column (price). And so on for each value you need from the same row. Each VLOOKUP is independent; they just share the same lookup_value and table_array.

For users who want to convert this tutorial into a more advanced version, the next concept is using Excel Tables. Convert your price list to an Excel Table by selecting the range and pressing Ctrl+T. Excel converts the range into a Table with structured references. Now write the VLOOKUP as =VLOOKUP(D2, PriceTable, 2, FALSE) using the Table name instead of $A$2:$B$11. The Table self-extends as you add new products. The formula does not need updating; it picks up new rows automatically.

For users with Excel 365 or Excel 2021, the modern alternative is XLOOKUP. Replace VLOOKUP with XLOOKUP using a slightly different syntax: =XLOOKUP(D2, $A$2:$A$11, $B$2:$B$11, "Not Found") looks up D2 in the price column and returns the corresponding price, with "Not Found" displayed if the lookup fails. XLOOKUP handles left-side lookups and exact match by default — cleaner than VLOOKUP for new formulas in supported Excel versions.

For learners practicing this tutorial, repeat it with three different datasets to internalize the pattern. First, the price list and order from this tutorial. Second, an employee directory and time sheet. Third, a shipping rate table and order list. After three iterations, the VLOOKUP formula structure becomes muscle memory.

Most Excel users who have written 50 to 100 VLOOKUPs no longer think about syntax — they think about the data and the formula appears automatically through years of repeated practical use across daily spreadsheet work, business analysis and many similar real-world data tasks that repeat across nearly every Excel spreadsheet seen in modern offices.

For learners moving from this beginner tutorial to advanced topics, the natural progressions are XLOOKUP (in Excel 365), INDEX/MATCH (universal alternative), conditional VLOOKUP with array formulas, and managing reference data through Excel Tables. Each topic builds on this VLOOKUP foundation. The investment in understanding VLOOKUP thoroughly pays back across years of Excel work because the same lookup-by-key pattern appears in nearly every business spreadsheet.