How to Use VLOOKUP in Excel Without #N/A Errors

TL;DR

Use VLOOKUP by supplying a lookup value, table array, return column number, and exact or approximate match setting. When copying the formula, lock the table array with absolute references to prevent it from shifting, or convert the source range into an Excel table so the reference stays accurate when rows are added or removed.

Transcript

Today, we're going to learn how to use VLOOKUP  in Excel. Let's dive in. Here I am in Microsoft Excel. Over on the left-hand side, I have a table  with cookie names and also their prices. And over here, I have a list of cookies and I need to fill  in the price. Now here, I need to know the price of classic chocolate chip cookie. I can look  over he... Read More

Key Insights

• VLOOKUP is a vertical lookup function that searches a table for a specified value and returns related information from another column. In the example, it finds a cookie name and retrieves the corresponding price, avoiding the need to enter each price manually.
• A VLOOKUP formula starts with an equal sign because Excel uses that character to recognize a formula. The function name and opening parenthesis follow, after which Excel displays a helpful prompt listing the arguments required for the function to work.
• The lookup value is the item that VLOOKUP must find in the source table. For the demonstrated calculation, the lookup value is the cell containing the classic chocolate chip cookie name, and selecting that cell inserts its reference into the formula.
• The table array is the complete source range that VLOOKUP searches. It must include the column containing the lookup value and the column containing the desired result, such as the table holding both cookie names and their prices.
• The column index number identifies which column within the selected table contains the result. Because cookie names occupy the first column and prices occupy the second, entering 2 instructs VLOOKUP to return a value from the price column.
• FALSE requests an exact match between the lookup value and the source table entry. This setting is appropriate when the cookie name must be found exactly as written, while the alternative setting allows an approximate match.
• Relative references move when a formula is copied to other rows. In the example, copying the formula down by two rows also shifts the table range by two rows, excludes the double chocolate chunk cookie, and produces a #N/A error.
• An Excel table provides a reference that continues to cover the appropriate list when cookies are added or removed. This approach avoids repeatedly updating a fixed absolute range, while still allowing the VLOOKUP formula to be copied down successfully.

Questions & Answers

Q: How do you create a VLOOKUP formula in Excel?

Start the formula with an equal sign, type VLOOKUP, and open the parentheses. Select the cell containing the value you want to find, enter a comma, and select the source table. Then enter the return column number, add another comma, specify the match type, close the parentheses, and press Enter to calculate the result.

Q: What arguments does VLOOKUP require in Excel?

VLOOKUP requires a lookup value, a table array, a column index number, and a match setting. The lookup value is the item being searched for. The table array contains the source data. The column index identifies the result column, and the final argument specifies whether the match should be approximate or exact.

Q: What is the lookup value in a VLOOKUP formula?

The lookup value is the item that Excel should find in the source table. In the demonstrated worksheet, it is a cookie name from the list that needs a price. Selecting the cookie cell inserts its cell reference into the formula, allowing VLOOKUP to search for that name in the source table.

Q: How does the VLOOKUP column index number work?

The column index number tells VLOOKUP which column in the selected table contains the value to return. Columns are counted from the first column of that table. In the example, cookie names are in the first column and prices are in the second, so the formula uses 2 to return a price.

Q: How do you request an exact match with VLOOKUP?

Use FALSE as the final VLOOKUP argument when the lookup value must match an entry exactly as written. In the cookie example, FALSE instructs Excel to locate the exact cookie name in the source table. After entering FALSE, close the parentheses and press Enter to return the corresponding price.

Q: Why does VLOOKUP return #N/A after copying a formula?

A copied VLOOKUP formula can return #N/A when its table array uses relative references. As the formula moves down, the referenced source range moves by the same number of rows. If that shifted range no longer contains the requested item, VLOOKUP cannot find a match and returns the #N/A error.

Q: How do you lock the VLOOKUP table array when copying formulas?

Click inside the formula, select each table reference, and press F4. Excel adds dollar signs before the relevant column and row references, changing the table array from a relative reference to an absolute reference. The locked range then remains fixed when the VLOOKUP formula is copied into other rows.

Q: Why should you use an Excel table with VLOOKUP?

An Excel table gives VLOOKUP a source reference that continues to cover the correct list when rows are added or removed. Convert the data range through Insert, confirm that the table has headers, and reference the resulting table in the formula. The copied formulas then keep searching the appropriate source data without manually revising a fixed range.

Summary & Key Takeaways

• VLOOKUP, meaning vertical lookup, can retrieve a value from a table instead of requiring manual entry. The demonstrated formula searches for a cookie name in the first column and returns its price from the second column, using FALSE to require an exact match with the source text.

• A VLOOKUP formula begins with an equal sign, followed by VLOOKUP and its required arguments inside parentheses. The arguments identify the value to find, the table to search, the numbered column containing the result, and whether Excel should accept an approximate match or require an exact match.

• Copying a formula can shift a relative table reference and produce a #N/A error because the referenced range may exclude required data. Pressing F4 adds dollar signs that create an absolute reference. Converting the source range into an Excel table offers a more flexible reference when rows are added or removed.