How to use VLOOKUP in Excel (with useful tips and syntax)

By Indeed Editorial Team

Updated 17 June 2022 | Published 3 January 2022

Updated 17 June 2022

Published 3 January 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Microsoft Excel's ability to build spreadsheets and use VLOOKUP is an excellent way you could gain the most out of your data. Data collection, sorting and categorisation is an essential business function for many firms. Stakeholders use insights from data relating to sales figures, staff pay, customer happiness scores and other metrics to make critical business decisions. In this article, we discuss how to use VLOOKUP in Excel, the syntax you would use and valuable tips to get the most by using VLOOKUP.

How to use VLOOKUP in Excel

Follow these steps on how to use VLOOKUP in Excel:

1. Create a spreadsheet or table

You can open a data table to use the VLOOKUP function in Excel if you already have one or create a spreadsheet. Ensure to organise the data table vertically with your data in rows to make the lookup values appear to the right of your chosen column. When your lookup value is in the left column, a search may be challenging to achieve. For example, if you look up the value in the fifth column, it may be hard to use the VLOOKUP function to pull data from columns one through to four.

2. Decide what type of information you wish to get

To use VLOOKUP, you may first know what data you're looking for. The information may assist you in determining how to design your function to retrieve the information you require. You may reorganise your table to get relevant results from your search query.

3. Make a list of your arguments.

VLOOKUP uses arguments to provide the data you require. To gain an exact match or an approximate value of what you want, you may want to know and use the four critical pieces of information which include:

  • The Lookup value argument that specifies which data point you are looking for in your table.

  • The table array option that specifies the number of cells in your data table.

  • The column index that lists the columns in which you perform VLOOKUP search while counting from left to right.

  • Range lookup which tells VLOOKUP whether you want a close or precise match. If you use 'TRUE' or '1' in your query, you're OK with an approximate match and 'FALSE' or '0' shows that you desire an exact match.

4. Create a VLOOKUP query

You may type your VLOOKUP query in the formula bar at the top of your screen once you have the information for the four arguments ready. You could, for example, write your function as:

=VLOOKUP(G4,A1:E9,2,FALSE)

With the result's input, it's possible to interpret the function as follows:

  • You desire the results to appear in your table's G4 cell

  • The cells A1 to E9 in your table contain data

  • VLOOKUP may get data from the second column.

  • You would have a perfect fit

VLOOKUP's default is to deliver approximate matches every time until you specify, which can be difficult with some data sets.

Related: How to become a data analyst

What is VLOOKUP?

VLOOKUP is a function that allows you to query and source data from a vertically organised data set.

The following is the syntax for VLOOKUP:

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

VLOOKUP arguments:

  • lookup value = the value in the data collection that you desire to find (first column)

  • column index num = the column in the table where you may search for the return value and retrieve.

  • Array table = the range or table where you store lookup value

  • rangelookup (optional) = TRUE (close match) or FALSE (no match or exact match)

Even if your search parameters differ from your data collection, VLOOKUP may still offer you results.

What to consider when using VLOOKUP

Check if the data you are pulling to and from your spreadsheet is arranged in vertical columns and the value you are looking for in the first column of the table. It is important to note that VLOOKUP allows you to query from the column on the right of the data. When you have a partial match, you can use wildcards in the VLOOKUP function. In Microsoft Excel, the VLOOKUP function helps you to:

  • find information in a large spreadsheet

  • join two bits of information together

  • combine data from many tables

  • sort information into new categories

  • assign values to each item of data.

Insights on how VLOOKUP works

Understanding how VLOOKUP works may give you an insight into how to handle different data sets. Your confidence and knowledge in interacting with extensive data may improve. The following are insights on how VLOOKUP works:

Arrangement order

VLOOKUP relies on column numbers. Imagine that every column in the table array has numbers from left to right. VLOOKUP provides a number for column index number to get a value from a specific column. It is also important to note that VLOOKUP looks at the data on the right column to the right of the lookup value.

Matching modes

Match modes enable VLOOKUP to match a range of values instead of an exact value when is set to TRUE. The fourth input, range lookup, controls the exact and approximate matching modes. You may use the same match option when you have a unique key to use as a lookup value and approximate match when you want the best match.

The first match only locates the best match. If there are many matching values, you can use wildcards match when you require a partial match on a lookup value. For example, you may use VLOOKUP to get data from a table using a partial lookup value and wildcard. If you want to use wildcards, set the lookup range to FALSE to get an exact match.

Designed functions

Two-way lookup uses the column index number, which is usually hard-coded as a static number inside the VLOOKUP method. You can establish a dynamic column index by locating the column you require using the match function. You may use this technique to generate a dynamic two-way lookup that matches on both rows and columns.

With Multiple criteria, you may find minimal support by the VLOOKUP function. You can use a helper column to link many columns together and use these data as multiple criteria inside VLOOKUP.

VLOOKUP and #N/A error function is necessary to identify a #N/A error which stands for 'not found.' You can decide to do a double search to confirm if the error is still available.

Related: How to become a cloud engineer (with skills and salaries)

Valuable tips for using VLOOKUP in Microsoft Excel

Best practice for the VLOOKUP function when finding information in a large spreadsheet or always seeking the same type of information is essential. These tips are helpful if you plan to use the process in your data:

  • Make several lookup tables: Create two ranges on the spreadsheet for clarity. You can then use a VLOOKUP function that considers both tables and relies on the information you want to get.

  • Perform a combined lookup: By Using the match function, the column may change into a dynamic column index. You can use the match function in combination with VLOOKUP to match data in your table's rows and columns.

  • Investigate #N/A or ERROR return: When you get the #N/A error after running your query, it simply implies the value you're seeking isn't in the table. You can merely check again if not satisfied or apply another function if possible.

  • Improve the appearance of #N/A mistakes: If you wish to improve your table's appearance, enter 'Not found' into your VLOOKUP query. This would replace all instances of #N/A in your table with 'Not found' making them look neat.

  • Check for any missing or incorrect characters: Remove any invalid characters from your data to ensure that VLOOKUP performs well. Extra spaces, quotation marks and other characters that contribute to the data you're using are examples of characters to eliminate by using the clean or trim operations.

Frequently asked questions

The following are some of the frequently asked questions about VLOOKUP:

1. What is not possible with VLOOKUP?

In the array table, the lookup value may be absent in the first column. VLOOKUP has a limitation in that it can only look for values in the table array's left-most column. The #N/A error may appear if your lookup value is not in the first column of the display.

2. How can you avoid N in VLOOKUP?

You can use the IFERROR function to catch the #N/A error and hide it. VLOOKUP shows the N error when it can't find a value in a lookup table. In this case, if VLOOKUP returns a value, there is no N error but if it returns the #n/A error, the IFERROR may take over to return the value you like.

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Explore more articles