Skip to content
install7.com

How To Use The VLOOKUP Function In Excel. Tutorial 2021

Would you like to know how to use the VLOOKUP function in Excel? In this tutorial we will show you how to use the VLOOKUP function to copy data from another worksheet or workbook, Vlookup across multiple sheets and dynamically search to return values ​​from different sheets to different cells.

When looking up information in Excel, it is rare that all the data is on the same sheet. More often, you will have to search across multiple sheets or even different workbooks. The good news is that Microsoft Excel provides more than one way to do this, and the bad news is that all the forms are a bit more complicated than a standard VLOOKUP formula. But with a little patience, you will learn.

How to use VLOOKUP function in Excel between two sheets

To start, we will put a simple case: use the VLOOKUP function in Excel to copy data from another worksheet. It is very similar to a normal VLOOKUP formula looking in the same worksheet. The difference is that it includes the sheet name in the argument table_array to tell your formula which worksheet the search range is on.

Here you can learn about: How to Improve Your Excel Experience – 13 Helpful Tips

The generic formula for VLOOKUP function in Excel from another sheet is the following:

  • VLOOKUP (lookup_value, Sheet! Range, col_index_num, [range_lookup])

As an example, let’s extract the sales figures from the January report to the Summary sheet. To do this, you must define the following arguments:

  • Search values are in column A of the sheet Summary and we mean the first data cell, which is A2.
  • Table_arrayis range A2: B6 on the January sheet. To refer to it, you must prepend the reference of the range with the name of the sheet followed by the exclamation point: Jan! $ A $ 2: $ B $ 6.

NOTE: Here you should pay attention that you lock the range with absolute cell references to prevent it from changing when copying the formula to other cells.

  • Col_index_numis 2 because you want to copy a value from column B, which is the second column in the table array.
  • Range_lookupset to FALSE to find an exact match.

Putting the arguments together, you get this formula:

  • = VLOOKUP (A2, Jan! $ A $ 2: $ B $ 6, 2, FALSE)

Drag the formula down the column and you will have this result:

Similarly, you can make use of the Vlookup function data from the February and March sheets:

  • = VLOOKUP (A2, Feb! $ A $ 2: $ B $ 6, 2, FALSE)
  • = VLOOKUP (A2, Mar! $ A $ 2: $ B $ 6, 2, FALSE)

VLOOKUP (A2, Mar! $ A $ 2: $ B $ 6, 2, FALSE)

Tips and notes on the VLOOKUP function in Excel:

  • If the sheet name contains spacesor characters not alphabetical, it must be enclosed in single quotes, such as ‘January sales’! $ A $ 2: $ B $ 6.
  • Instead of typing the name of a sheet directly into a formula, you can switch to the search worksheet and select the range there. Excel will insert a reference with the correct syntax automatically, saving you the hassle of verifying the name and fixing the problem.

Methods to use the VLOOKUP function in Excel

Now, let’s get into the subject of using the VLOOKUP function in Excel. here are some options:

Method 1: Vlookup from a different workbook

To use the VLOOKUP function in Excel between two workbooks, you must enclose the file name in brackets, followed by the sheet name and the exclamation point.

  • For example: to find the value A2 in the range A2: B6 in the January sheet in the workbook xlsx, use this formula:
  • = VLOOKUP (A2, [Sales_reports.xlsx]Jan! $ A $ 2: $ B $ 6, 2, FALSE)

Method 2: Vlookup on multiple sheets with IFERROR

When you need to search between more than two sheets, the simplest solution is to use the VLOOKUP function in Excel in combination with IFERROR. The idea is to nest multiple IFERROR functions to check multiple worksheets one by one: if the first VLOOKUP doesn’t find a match on the first sheet, search on the next sheet, and so on. For instance:

  • ERROR (VLOOKUP (…), IF.ERROR (VLOOKUP (…),…, ” Not found“))

To see how this approach works on real-life data, let’s consider the following example:

Below is the table of abstract that you must complete with the names of the articles and the quantities when looking for the order number in the West and East sheets:

Summary table

  • Step 1: First, you are going to remove the elements. To do this, we indicate the formula VLOOKUP look for the order number in A2 on the sheet East and return the value of column B (2nd column in table_array A2: C6).
  • Step 2: If no exact match is found, search the sheet West.
  • Step 3: Yes, both Vlookups fail, it will return the message: “Not found”.
    • = IFERROR (VLOOKUP (A2, East! $ A $ 2: $ C $ 6, 2, FALSE), IFERROR (VLOOKUP (A2, West! $ A $ 2: $ C $ 6, 2, FALSE), “Not found”))

IFERROR (VLOOKUP (A2, East! $ A $ 2: $ C $ 6, 2, FALSE), IFERROR (VLOOKUP (A2, West! $ A $ 2: $ C $ 6, 2, FALSE), "Not found"))

  • Step 4: To return the quantity, simply change the index number of the column to 3:
    • = IFERROR (VLOOKUP (A2, East! $ A $ 2: $ C $ 6, 3, FALSE), IFERROR (VLOOKUP (A2, West! $ A $ 2: $ C $ 6, 3, FALSE), “Not found”))

Tip: If necessary, you can specify different table arrays for different VLOOKUP functions. In this example, both search sheets have the same number of rows (A2: C6), but your worksheets can be a different size.

Method 3: Vlookup in Multiple Workbooks

To search between two or more workbooks, you must enclose the workbook name in brackets and place it before the sheet name.

  • For instance: this is how you can use Vlookup intwo different files (Book1 and Book2) with a single formula:
    • = IFERROR (VLOOKUP (A2, [Book1.xlsx]East! $ A $ 2: $ C $ 6, 2, FALSE), IFERROR (VLOOKUP (A2, [Book2.xlsx]West! $ A $ 2: $ C $ 6, 2, FALSE), “Not found”))

Method 4: Make column index number dynamic for Vlookup multiple columns

In a situation where you need to return data from multiple columns, do that col_index_num be dynamic could save you some time. There are a couple of adjustments to make:

  • For the col_index_num argument, use the function COLUMNS which returns the number of columns in a specified array: COLUMNS ($ A $ 1: B $ 1). (The row coordinate doesn’t really matter, it can be any row.)
  • In the lookup_value argument, locks the column reference with the $ sign ($ A2), so it remains fixed when copying the formula to other columns.

As a result, you will get a kind of dynamic formula that extracts matching values ​​from different columns, depending on which column the formula is copied into:

  • = IFERROR (VLOOKUP ($ A2, East! $ A $ 2: $ C $ 6, COLUMNS ($ A $ 1: B $ 1), FALSE), IFERROR (VLOOKUP ($ A2, West! $ A $ 2: $ C $ 6, COLUMNS ( $ A $ 1: B $ 1), FALSE), “Not found”))

When entered in column B, COLUMNS ($ A $ 1: B $ 1) evaluates to 2 and tells VLOOKUP to return a value of 2 second column in the table matrix.

When copied to column C (that is, you have dragged the formula from B2 to C2), B $ 1 changes to C $ 1 because the column reference is relative. Consequently, COLUMNS ($ A $ 1: C $ 1) evaluates to 3, forcing the VLOOKUP function in Excel to return a value of the third column.

COLUMNS ($ A $ 1: C $ 1)
COLUMNS ($ A $ 1: C $ 1)

This formula works great for 2-3 search sheets. If you have more, repetitive IFERRORs get too cumbersome. The following example demonstrates a slightly more complicated but much more elegant approach.

Method 5: Vlookup multiple sheets with INDIRECT

One more way to Vlookup between multiple sheets in Excel is to use a combination of VLOOKUP and INDIRECT functions. This method requires a bit of preparation, but in the end, you will have a more compact formula for Vlookup on any number of spreadsheets.

A generic formula for Vlookup in sheets is as follows:

  • VLOOKUP ( lookup_value, INDIRECT (“‘” & INDEX ( Lookup_sheets , MATCH (1, – (COUNTIF (INDIRECT (“‘” & Sheet_list & “‘! Lookup_range “), lookup_value )> 0), 0)) & “‘! table_array “), col_index_num , FALSE)

Where:

  • Lookup_sheets– A named range consisting of the names of the lookup sheets.
  • Lookup_value: the value to look for.
  • Lookup_range– The range of columns in the lookup sheets to search for the lookup value.
  • Table matrix: the range of data in the search sheets.
  • Col_index_num: the number of the column in the table array from which a value is returned.

For the formula to work properly, you must be aware of the following caveats:

  1. This is an array formula, which must be completed by pressing Ctrl + Shift + Enter keys together.
  2. All sheets must have the same column order.
  3. Since you use a table array for all lookup sheets, specify the larger range if your sheets have different number of rows.

How to use the formula for Vlookup in sheets

To use the Vlookup function on multiple sheets at the same time, you must follow these steps:

  • Step 1: Write all the search sheet names somewhere in your workbook and name that range (Lookup_sheets in this case).
Lookup_sheets
Lookup_sheets
  • Step 2: Adjust the generic formula for your data. In this example it would be:
    • looking for the value A2 (lookup_value)
    • in the range A2: A6 (lookup_range) in four worksheets (East, North, South West), and
    • extract matching values ​​from column B, which is column 2 (сol_index_num) in the data range A2: C6 (table_array).

With the above arguments, the formula takes this form:

  • = VLOOKUP ($ A2, INDIRECT (“‘” & INDEX (Lookup_sheets, MATCH (1, – (COUNTIF (INDIRECT (“‘” & Lookup_sheets & “‘! $ A $ 2: $ A $ 6”), $ A2)> 0) , 0)) & “‘! $ A $ 2: $ C $ 6”), 2, FALSE)

NOTE: Please note that we lock both ranges ($ A $ 2: $ A $ 6 and $ A $ 2: $ C $ 6) with absolute cell references.

  • Step 3: Enter the formula in the top cell (B2 in this example) and press Ctrl + Shift + Enter to complete it.
  • Step 4: Double-click or drag the fill handle to copy the formula down the column.

As a result, you will have the formula to find the order number on 4 sheets and retrieve the corresponding item. If a specific order number cannot be found, an error is displayed # N / A as in row 14:

error # N / A
error # N / A

To return the quantity, just replace 2 with 3 in the argument col_index_num since the quantities are in the 3rd column of the table matrix:

  • = VLOOKUP ($ A2, INDIRECT (“‘” & INDEX (Lookup_sheets, MATCH (1, – (COUNTIF (INDIRECT (“‘” & Lookup_sheets & “‘! $ A $ 2: $ A $ 6”), $ A2)> 0 ), 0)) & “‘! $ A $ 2: $ C $ 6”), 3, FALSE)

If you want to override the standard error notation # N / A With your own text, wrap the formula in the IFNA function:

  • = IFNA (VLOOKUP ($ A2, INDIRECT (“‘” & INDEX (Lookup_sheets, MATCH (1, – (COUNTIF (INDIRECT (“‘” & Lookup_sheets & “‘! $ A $ 2: $ A $ 6”), $ A2) > 0), 0)) & “‘! $ A $ 2: $ C $ 6”), 3, FALSE), “Not found”)
IFNA (VLOOKUP ($ A2, INDIRECT ("'"& INDEX (Lookup_sheets, MATCH (1, - (COUNTIF (INDIRECT ("'" & Lookup_sheets & "'! $ A $ 2: $ A $ 6"), $ A2)> 0), 0)) & "’! $ A $ 2: $ C $ 6"), 3, FALSE), "Not found")” width=”700″ height=”450″  data-lazy- src=”https://install7.com/wp-content/uploads/1630191719_879_How-To-Use-The-VLOOKUP-Function-In-Excel-Tutorial-2021.jpg”/><noscript><img loading=Not found – not found

Vlookup multiple sheets between workbooks

This generic formula (or any variation) can also be used to display multiple sheets in one different workbook. For this, concatenate the name of the workbook inside INDIRECT as shown in the following formula:

  • = IFNA (VLOOKUP ($ A2, INDIRECT (“‘[Book1.xlsx]”& INDEX (Lookup_sheets, MATCH (1, – (COUNTIF (INDIRECT (” ‘[Book1.xlsx]”& Lookup_sheets &” ‘! $ A $ 2: $ A $ 6 “), $ A2)> 0), 0)) &”‘! $ A $ 2: $ C $ 6 “), 2, FALSE),” Not found ” )

Vlookup between sheets and returns multiple columns

If you want to extract data from multiple columns, one multi-cell array formula you can do it at once. To create such a formula, supply an array constant for the argument col_index_num.

In this example, we are going to return the names of the elements (column B) and the quantities (column C), which are the 2nd and 3rd columns in the table matrix, respectively. So the required matrix is {2,3}.

  • = VLOOKUP ($ A2, INDIRECT (“‘” & INDEX (Lookup_sheets, MATCH (1, – (COUNTIF (INDIRECT (“‘” & Lookup_sheets & “‘! $ A $ 2: $ C $ 6”), $ A2)> 0 ), 0)) & “‘! $ A $ 2: $ C $ 6”), {2,3}, FALSE)

To correctly enter the formula in multiple cells, this is what you need to do:

  • Step 1: In the first row, select all the cells to be filled (B2: C2 in this example).
  • Step 2: Write the formula and press the keys Ctrl + Shift + Enter. This enters the same formula in the selected cells, which will return a different value in each column.
  • Step 3: Drag the formula to the remaining rows.

IFNA (VLOOKUP ($ A2, INDIRECT ("'"& INDEX (Lookup_sheets, MATCH (1, - (COUNTIF (INDIRECT ("'" & Lookup_sheets & "'! $ A $ 2: $ A $ 6"), $ A2)> 0), 0)) & "’! $ A $ 2: $ C $ 6"), 3, FALSE), "Not found")” width=”700″ height=”450″  data-lazy- src=”https://install7.com/wp-content/uploads/1630191719_953_How-To-Use-The-VLOOKUP-Function-In-Excel-Tutorial-2021.jpg”/></p>
<p><noscript><img loading=How the VLOOKUP function formula works in Excel

To better understand the logic, we are going to analyze this basic formula in the individual functions:

  • = VLOOKUP ($ A2, INDIRECT (“‘” & INDEX (Lookup_sheets, MATCH (1, – (COUNTIF (INDIRECT (“‘” & Lookup_sheets & “‘! $ A $ 2: $ A $ 6”), $ A2)> 0) , 0)) & “‘! $ A $ 2: $ C $ 6”), 2, FALSE)

Working from the inside out, this is what the formula does:

COUNTIF and INDIRECT

In short, INDIRECT creates the references for all the lookup sheets, and COUNTIF counts the occurrences of the lookup value (A2) on each sheet:

  • – (COUNTIF (INDIRECT (“‘” & Lookup_sheets & “‘! $ A $ 2: $ A $ 6”), $ A2)> 0)

More details:

First, concatenate the name of the range (Lookup_sheets) and the range reference ($ A $ 2: $ A $ 6), adding apostrophes and the exclamation point in the correct places to make an external reference, and feed the resulting text string to the INDIRECT function to dynamically reference lookup sheets:

  • INDIRECT ({“‘East’! $ A $ 2: $ A $ 6”; “‘South’! $ A $ 2: $ A $ 6”; “‘North’! $ A $ 2: $ A $ 6”; “‘West’! $ A $ 2: $ A $ 6 “})

TELL.YES checks every cell in range A2: A6 on each lookup sheet with the value in A2 on the parent sheet and returns the match count for each sheet.

In this data set, the order number in A2 (101) is in the leaf West, which is 4 º in the named range, so COUNT YES returns this array:

Next, compare each element of the above array with 0:

This produces an array of values TRUE (greater than 0) and FALSE (equal to 0), which coerces 1 and 0 by using a unary double (-), and you get the following array as a result:

This operation is an additional precaution in handling a situation where a lookup sheet contains multiple occurrences of the lookup value, in which COUNTIF case would return a count greater than 1, while you just want 1 and 0 in the final matrix (in a moment, you’ll understand why).

After all these transformations, the formula looks like this:

  • VLOOKUP ($ A2, INDIRECT (“‘” & INDEX (Lookup_sheets, MATCH (1, {0; 0; 0; 1}, 0)) & “‘! $ A $ 2: $ C $ 6”), 2, FALSE)

INDEX and MATCH

At this point, a classic combination of INDEX ITEM pass to:

  • INDEX (Lookup_sheets, MATCH (1, {0; 0; 0; 1}, 0))

The function COINCIDE set for exact match (0 in last argument) finds the value 1 in array {0; 0; 0; 1} and returns its position, which is 4:

INDEX (Lookup_sheets, 4)

The function INDEX use the number returned by COINCIDE as the row number argument (row_num) and returns the fourth value in the named range Lookup_sheets, What is it West.

So the formula further reduces to:

  • VLOOKUP ($ A2, INDIRECT (“‘” & “West” & “‘! $ A $ 2: $ C $ 6”), 2, FALSE)

VLOOKUP and INDIRECT

The function INDIRECT process the text string inside it:

  • INDIRECT (“‘” & “West” & “‘! $ A $ 2: $ C $ 6”)

And turns it into a reference that goes to the argument table_array from VLOOKUP:

  • VLOOKUP ($ A2, ‘West’! $ A $ 2: $ C $ 6, 2, FALSE)

Finally, this very standard VLOOKUP formula finds the value A2 in the first column of the range A2: C6 on the West sheet and returns a match from the second column.

Dynamic VLOOKUP to return data from multiple sheets to different cells

First, let’s define what exactly the word means “dynamic” in this context and how this formula will differ from the previous ones.

In case you have large amounts of data in the same format that are divided into multiple worksheets, you may want to extract information from different sheets in different cells. The following image illustrates the concept:

Dynamic VLOOKUP

Unlike previous formulas that retrieved a value from a specific sheet based on a unique identifier, this time you will seek to extract values ​​from several sheets at the same time.

There are two different solutions for this task. In both cases, you need to do a little preparatory work and create named ranges for the data cells on each lookup sheet. For this example, we define the following ranges:

  • East_Sales- A2: B6 on the East sheet
  • North_Sales- A2: B6 on the North sheet
  • South_Sales- A2: B6 on the South sheet
  • West_Sales- A2: B6 on the West sheet

Nested VLOOKUP and IFs

If you have a reasonable number of sheets to search for, you can use nested IF functions to select the sheet based on the keywords in the predefined cells (cells B1 to D1 in this case).

With the search value in A2, the formula is as follows:

  • = VLOOKUP ($ A2, IF (B $ 1 = “east”, East_Sales, IF (B $ 1 = “north”, North_Sales, IF (B $ 1 = “south”, South_Sales, IF (B $ 1 = “west”, West_Sales) ))), 2, FALSE)

Translated into English, the part IF says:

If B1 is East, searches the range named East_Sales; if B1 is North, search in the range called North_Sales; if B1 is South, search in the range called South_Sales; and if B1 is West, search in the range called West_Sales.

The range returned by IF goes to table_array from VLOOKUP, which extracts a matching value from the second column on the corresponding sheet.

Smart use of mixed references for lookup value ($ A2 – absolute column and relative row) and IF logical test (B $ 1 – relative column and absolute row) allows to copy formula to other cells without any changes – Excel adjusts the references automatically based on the relative position of a row and column.

So, you enter the formula in B2, copy it to the right and down in as many columns and rows as necessary, and you will get the following result:

formula in B2
Formula in B2

INDIRECT VLOOKUP

When working with many sheets, multiple nested levels can make the formula too long and difficult to read. A much better way is to create a dynamic vlookup range with the help of INDIRECT:

  • = VLOOKUP ($ A2, INDIRECT (B $ 1 & “_ Sales”), 2, FALSE)

Here, we concatenate the reference to the cell that contains a unique part of the named range (B1) and the common part (_Sales). This produces a text string like “East_Sales”, which INDIRECT converts to the range name understandable for Excel.

As a result, you get a compact formula that works wonderfully on any number of sheets:

Search Result
Search Result

You may be interested in knowing about: How to Group a Pivot Table By Months In Excel

As you will see, these are the ways to use the VLOOKUP function in Excel together with other search functions by sheet such as Vlookup that can help you a lot to find the data you want to achieve between sheets and files in Excel. We hope we have helped you.