You can use VLOOKUP to search for values in a different tab or sheet in Excel. You can use VLOOKUP to search for values in a different tab in Excel by specifying the tab name in square brackets before the table range.
How to Vlookup other Tab
Make sure that the two tabs or sheets you want to reference are in the same workbook.
In the cell where you want to display the result of the VLOOKUP function, type the formula
=VLOOKUP(lookup_value, sheetname!table_array, col_index_num, range_lookup)
- “lookup_value” with the value you want to search for,
- “sheetname” with the name of the sheet or tab where the data is located,
- “table_array” with the range of cells that contains your data (including column headers),
- “col_index_num” with the number of the column that contains the value you want to return,
- “range_lookup” with either TRUE or FALSE depending on whether you want an approximate or exact match.
Example of Vlookup other Tab
Let’s say you have two tabs in your workbook – one called “SalesData” and another called “SalesReport”. In the “SalesData” tab, you have a table that lists the sales data for each day of the month.
You want to find the sales data for January 15, 2023, which is located in column B. In the “SalesReport” tab, you want to display the sales data in cell A1.
- First, switch to the “SalesReport” tab.
- In cell A1, type the formula “=VLOOKUP(44282, SalesData!A2:B32, 2, FALSE)”.
- This formula uses the serial number 44282 (which corresponds to January 15, 2023) as the lookup value, specifies the range A2:B32 on the “SalesData” tab as the table_array, specifies 2 as the column index number to return the sales data in column B, and specifies FALSE to require an exact match.
- When you press Enter, the formula will return the sales data for January 15, 2023, from the “SalesData” tab, and display it in cell A1 on the “SalesReport” tab.
If the name of the tab contains spaces, you should enclose it in single quotes within the VLOOKUP formula. For example, if your tab is named “Sales Data,” you should use ‘Sales Data’! in your formula.