If the specific value you lookup doesn't exist, a #N/A value will be displayed. If you have more worksheets, you just need to add the vlookup function in conjunction with the IFERROE after the formula.ģ. For much easier to understand this formula, in fact, the long formula is composed by several vlookup function and connect with the IFERROR function. 2: indicates the column number that your matched value is returned.Ģ.A2:B6: is the data range of the worksheets you need to search.Sheet1, Sheet2, Sheet3: are the sheet names which include the data you want to use.

A2: is the cell reference which you want to return its relative value.

And then drag the fill handle down to the range of cells you want to contain this formula, see screenshot: And then you can enter the following long formula into your specific cell: 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.Ģ. 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:ġ. Vlookup values from multiple worksheets with an awesome feature Vlookup values from multiple worksheets with a normal formula Vlookup values from multiple worksheets with an array formula

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: In excel, we can easily apply the vlookup function to return the matching values in a single table of a worksheet. How to vlookup values across multiple worksheets?

Excel vlookup example pdf