How to Use VLOOKUP in Excel

Computing large amounts of data in Excel can be overwhelming and time-consuming, which is why automation is helpful. If you regularly use Excel, you will be familiar with all its features that let you calculate and work out numbers quickly. But if you’re using multiple spreadsheets and need to consult one, finding the figures manually can be frustrating.

This is where VLOOKUP comes in. Vertical lookup or VLOOKUP helps you to pick out specific information from a huge block of data. In this article, we explain how to use it.

How to Use VLOOKUP in Excel

Pick a Value

For this example, we’ll use smartphones as values. We’ll have iPhone (A2), Samsung (A3), Xiaomi (A4), Nokia (A5) and Google Pixel (A6). These are the lookup values you’ll be using to find data on each. You can have these anywhere you’re comfortable with, but for ease of navigation, let it appear close to the VLOOKUP value for which you’re searching.

Select a Range of Values

Pick a cell next to your value in which you want to create a range of values. Make sure you start your cell with (=VLOOKUP) and then select all the values you want. Your range will look like (A2:B5). In this case, A2 is in the value column and B5 will come from the range values you’re trying to find. At this stage, your formula will look like this: (=VLOOKUP (A5, A2:B5).

The A5 is the original value for which you’re seeking data. Always include it or your findings might be inaccurate.

Column of the Return Value

Now that you know the value you’re looking for and the selected range of values, you need to tell Excel the location of your return value. Pay attention here because you’ll not be using the usual naming system that Excel column uses. Counting from the left, pick a number within the range selected depending on the return value you’re interested in finding.

For example: (=VLOOKUP (A5, A2:B5; 3;0)

Getting Exact or Approximate Return Value

In the last step, you want to tell Excel to find you an approximate or exact value. Do this by typing “FALSE” or “TRUE.” Doing this will help you to find out if your argument is erroneous. This simply means that Excel can’t find matching data.

If your data is TRUE, Excel will give you a value that’s as close as possible to what you’re looking for, which simplifies everything.

If you follow these four functions as directed, you’ll get your lookup values and reduce the stress it would have taken you to find your values. At this point, you shouldn’t feel intimidated.

Why Is VLOOKUP Not Working?

When working with huge sets of data, it’s easy to get things mixed up especially if you don’t have them ordered properly. This can be the source of your problems with VLOOKUP. Here are some common errors you’re likely to run into using VLOOKUP.

Excel Can’t Find Value

If you fail to match the value on the spreadsheet with the one on your VLOOKUP function, you’ll be met with an #NA error message. This is because VLOOKUP doesn’t consider them a match. To fix this, you’ll need to key in your values again and make sure they match.

You Don’t Have a Value in the First Column

Before you enter any other arguments, you must have a value in the first column after you’ve opened the VLOOKUP bracket. Failure to do this results in the #NA error. Correct this by arranging your values in the proper order.

LOOKUP Value Is Small

If you get an appropriate match that’s smaller than all the other values you’re using on your table, you’ll get an #NA error.

Long LOOKUP Value

VLOOKUP supports up to 255 characters in length. If you input anything longer than that, Excel will return an #NA error. To correct that, make sure you stick to the appropriate length. Alternatively, if you must have a longer character length, use the INDEX, MATCH formula instead.

Numbers Appear as Text

Sometimes when using Excel, your numbers might change into text. If this happens, you’ll not be able to generate the values you’re seeking. Instead, an #NA error shows up. If this happens, convert all the text to numeric values first to get your values.

Spaces in LOOKUP Value

If you accidentally put in spaces between your values, you’ll get an #NA error. To correct this, put your lookup values in the =VLOOKUP formula and use the TRIM function to remove all the spaces.

Your Table Is Expanding

When adding extra rows, you also have to update your VLOOKUP so it includes them in the computing process. To do this, format the range you’re working with so that any changes will be added. To do this, choose the range of cells you want to format, go to Home, then Format as Table. Pick the style you want to use. In the Design area, change the name of your table.

VLOOKUP Only Works with Data on the Right

Make sure you arrange all your information to the right of your lookup value. VLOOKUP handles information starting with what’s on the far left and then tabulates it with what’s on the right. If you jumble up data, it’ll not account for what’s on the left, meaning you’ll get the wrong information.

Avoid Duplicates

VLOOKUP can’t separate information that looks the same. For example, if you have two brands of the same Smartphone – a Samsung Galaxy S22 and a Samsung Galaxy S22 Plus – VLOOKUP will use the one that appears first on the list. It’s up to you to distinguish the information.

Incorrect or Incomplete Paths

When using multiple spreadsheets and sent an incomplete reading from one of them, VLOOKUP will return a #VALUE error. Make sure your formula looks like this:

=VLOOKUP(lookup_value, '[workbook name]sheet name'!table_array, col_index_num, FALSE).

=VLOOKUP(lookup_value, ‘[workbook name]sheet name’!table_array, col_index_num, FALSE).

Understanding VLOOKUP in Excel

To effectively use VLOOKUP, your data on the spreadsheet must be arranged in a vertical way for identification. That noted, this feature uses four elements to pick out your data. Using this opener – =VLOOKUP(), you’ll need to key in your four elements in the brackets to get the data you’re looking for. The elements you’ll insert in the brackets include:

• The value you’re seeking
• The range of the value with which you are working
• The column where the return value is located
• An exact or approximate return value

Let’s write out the formula so you can see how it’ll appear in Excel:

 =VLOOKUP (Value, Range of Value, Column of return value, Exact Return Value (TRUE) or Approximate Return Value (FALSE).

If this sounds intimidating, think of it as an inventory where you keep the records of your products. It should be easier now that we’ve given you step-by-step guidance on how you should go about using these elements.

FAQ

How do you enable VLOOKUP in Excel?

You can enable VLOOKUP by going to the “Formula” tab in the Function Library, then “Insert Function.” Search for VLOOKUP or in “Select Category,” pick “Lookup & Reference.” On the “Select & Function,” select “VLOOKUP.” On the “Functions Argument” tab, type in the VLOOKUP Formula and click “OK.”

Manage Data Easily with VLOOKUP

If you’re just starting with data computation on Excel, VLOOKUP can be difficult to master. But if you’re an expert, you know how much time and effort it can save you. You don’t need to manually enter data or look for it. All you need is to use all the required functions that Excel provides to run through stacks of numbers.

If you run into problems, we’ve shown you what they are and how you can work around them. It can feel overwhelming but take your time. The more you use VLOOKUP, the better you’ll become.

What do you use VLOOKUP for? Share your thoughts in the comment section below.