How to vlookup values across multiple worksheets?

In excel, we can easily apply the vlookup function to return the matching values in a single table of a worksheet. But, have you ever considered that how to vlookup value across multiple worksheet? Supposing I have the following three worksheets with range of data, and now, I want to get part of the corresponding values based on the criteria from these three worksheets, see screenshots:

Vlookup values from multiple worksheets with an array formula

Vlookup values from multiple worksheets with a normal formula

Vlookup values from multiple worksheets with an awesome feature

Vlookup values from multiple worksheets with an array formula

To use this array formula, you should give these three worksheets a range name, please list your worksheet names in a new worksheet, such as following screenshot shown:

1. Give these worksheets a range name, select the sheet names, and type a name in the Name Box which next to the formula bar, in this case, I will type Sheetlist as the range name, and then press Enter key.

2. And then you can enter the following long formula into your specific cell:

3. And then, press Ctrl + Shift + Enter keys together to get the first corresponding value, then drag the fill handle down to the cells that you want to apply this formula, all the relative values of each row have been returned as follows:

Notes:

1. In the above formula:

• A2: is the cell reference which you want to return its relative value;
• Sheetlist: is the range name of the worksheet names I have created in step1;
• A2:B6: is the data range of the worksheets you need to search;
• 2: indicates the column number that your matched value is returned.

2. If the specific value you lookup doesn't exist, a #N/A value will be displayed.

Vlookup values from multiple worksheets with a normal formula

If you do not want to make the range name and are not familiar with the array formula, here also has a normal formula to help you.

1. Please type the following formula into a cell you need:

2. And then drag the fill handle down to the range of cells you want to contain this formula, see screenshot:

Notes:

1. In the above formula:

• A2: is the cell reference which you want to return its relative value;
• Sheet1, Sheet2, Sheet3: are the sheet names which include the data you want to use;
• A2:B6: is the data range of the worksheets you need to search;
• 2: indicates the column number that your matched value is returned.

2. For much easier to understand this formula, in fact, the long formula is composed by several vlookup function and connect with the IFERROR function. If you have more worksheets, you just need to add the vlookup function in conjunction with the IFERROE after the formula.

3. If the specific value you lookup doesn't exist, a #N/A value will be displayed.

Vlookup values from multiple worksheets with an awesome feature

Maybe the above two formulas are too difficult to use for you, here, I will introduce a powerful feature, Kutools for Excel's LOOKUP Across Multiple Sheets, with this feature, you can quickly deal with this job if there are dozens or hundreds of worksheets.

After installing Kutools for Excel, please do as this:

1. Click Kutools > Super Lookup > LOOKUP Across Multiple Sheets, see screenshot:

2. In the LOOKUP Across Multiple Sheets dialog box, please do the following operations:

• Select the lookup value cells and output cells from the Lookup values and Output Range section;
• Then, select and add the data range from other sheets into the Data range list box.

Note: If you want to replace the #N/A error value with another text value, you just need to check Replace #N/A error value with a specified value option, and then type the text you need.

3. Then, click OK button, all the matching records have been returned across multiple worksheets, see screenshots:

Click to Download Kutools for Excel and free trial Now!

More relative articles: