How do I fix name error in Excel Vlookup?

• Home
• Search
• About
• Services We Recommend
• Newsletter
• Start a Blog!
• Forum
• Donate

groovyPost

• SUBSCRIBE
• Best Of
• How To
• News
• Reviews
• YouTube
• Search   How-To

How to Troubleshoot VLOOKUP Errors in Excel

By Ben Stockton Last Updated on January 13, 2020

Struggling with the VLOOKUP function in Microsoft Excel? Here are some troubleshooting tips to help you out.

There are few things that bring out the sweats in Microsoft Excel users more than the thought of a VLOOKUP error. If youre not too familiar with Excel, VLOOKUP is one of the most difficult, or at least one of the least understood, functions available.

The purpose of VLOOKUP is to search for and return data from another column in your Excel spreadsheet. Unfortunately, if you get your VLOOKUP formula wrong, Excel will throw an error at you. Lets go through some common VLOOKUP errors and explain how to troubleshoot them.

Limitations of VLOOKUP

Before you begin using VLOOKUP, you should be aware that it isnt always the best option for Excel users.

To begin with, it cant be used to lookup data to the left of it. Itll also only display the first value it finds, meaning that VLOOKUP isnt an option for data ranges with duplicated values. Your search column also needs to be the furthest left column in your data range.

In the example below, the furthest column (column A) is used as the search column. There are no duplicated values in the range, and the lookup data (in this case, data from column B) is to the right of the search column.

The INDEX and MATCH functions would be good alternatives if any of these issues are a problem, as would the new XLOOKUP function in Excel, currently in beta testing.

VLOOKUP also requires data to be arranged in rows to be able to accurately search and return data. HLOOKUP would be a good alternative if this isnt the case.

There are some additional limitations to VLOOKUP formulae that can cause errors, as well explain further.

VLOOKUP and #N/A Errors

One of the most common VLOOKUP errors in Excel is the#N/Aerror. This error occurs when VLOOKUP cant find the value youre searching for.

To begin with, the search value might not exist in your data range, or you may have used the wrong value. If you see an N/A error, double-check the value in your VLOOKUP formula.

If the value is correct, then your search value doesnt exist. This assumes youre using VLOOKUP to find exact matches, with therange_lookup argument set to FALSE.

In the above example, searching for a Student ID with the number 104 (in cell G4) returns an #N/A error because the minimum ID number in the range is 105.

If therange_lookup argument at the end of your VLOOKUP formula is missing or set toTRUE, then VLOOKUP will return an #N/A error if your data range isnt sorted in ascending order.

It will also return an #N/A error if your search value is smaller than the lowest value in the range.

In the above example, the Student ID values are mixed up. Despite a value of 105 existing in the range,VLOOKUP cant run a correct search with therange_lookup argument set toTRUE becausecolumn A isnt sorted in ascending order.

Other common reasons for #N/A errors include using a search column that isnt the furthest left and using cell references for search values that contain numbers but are formatted as text, or contain surplus characters like spaces.

Troubleshooting #VALUE Errors

The#VALUE error is usually a sign that the formula containing the VLOOKUP function is incorrect in some way.

In most cases, this is usually because of the cell youre referencing as your search value. The maximum size of a VLOOKUP lookup value is 255 characters.

If youre dealing with cells that contain longer character strings, VLOOKUP wont be able to handle them.

The only workaround for this is to replace your VLOOKUP formula with a combined INDEX and MATCH formula. For example, where one column contains a cell with a string of more than 255 characters, a nested MATCH function inside of INDEX can be used to locate this data instead.

In the example below, INDEX returns the value in cell B4, using the range incolumn A to identify the correct row. This uses the nested MATCH function to identify the string incolumn A(containing 300 characters) that matches cell H4.

In this case, that is cellA4, withINDEX returning108 (the value ofB4).

This error will also appear if youve used an incorrect reference to cells in your formula, especially if youre using a data range from another workbook.

Workbook references need to be enclosed in square brackets for the formula to work correctly.

If you encounter a #VALUE error, double-check your VLOOKUP formula to confirm that your cell references are correct.

#NAME and VLOOKUP

If your VLOOKUP error isnt a #VALUE error or an #N/A error, then its probably a #NAME error. Before you panic at the thought of this one, rest assuredits the easiest VLOOKUP error to fix.

A #NAME error appears when youve misspelled a function in Excel, whether its VLOOKUP or another function like SUM. Click on your VLOOKUP cell and double-check that youve actually spelled VLOOKUP correctly.

If there are no other issues, your VLOOKUP formula will work once this error is corrected.

Using Other Excel Functions

Its a bold statement, but functions like VLOOKUP will change your life. At the very least, itll change your working life, making Excel a more powerful tool for data analysis.

If VLOOKUP isnt right for you, then take advantage of these top Excel functions instead.

Related Items:Microsoft Excel, Microsoft Office

Post navigationMicrosoft Ends Support for Windows 7 Today5 Ways to Use Zapier to Automate Your Website

Featured Articles

tips

This Credit Card Lifehack Saves Me Cash Every Single Day

This simple lifehack helps me maximize credit cards rewards programs for every purchase I make.

How-To

How to Make a Public Profile on Snapchat

Public profiles on Snapchat give you greater exposure and the chance to reach more users. Here's how to make a public profile...

How-To

How to Find Your Windows 11 Product Key

If you need to transfer your Windows 11 product key or just need it to do a clean install of the OS,...

How-To

Backblaze Cloud Backup Review  Updated 2021

Backing up your data to the cloud via an automated service is critical. Backblaze is the solution I use and recommend. Here's...

How-To

How to Clear Google Chrome Cache, Cookies, and Browsing History

Chrome does an excellent job of storing your browsing history, cache, and cookies to optimize your browser performance online. Hers's how to...

• Search
• About
• Contact
• Privacy Policy
• Terms of Service
• Staff
• Donate

Copyright © 2007-2021 groovyPost LLC | All Rights Reserved To Top

Video liên quan