VLOOKUP is a powerful function in Google Sheets and Microsoft Excel that allows you to search for a value in a table and return a corresponding value from another column. However, one of the limitations of the VLOOKUP function is that it can only search for values within the same sheet. But what if you need to VLOOKUP to a different sheet? Fortunately, there are several ways to achieve this. In this article, we will explore five ways to VLOOKUP to a different sheet.
Method 1: Using the VLOOKUP Function with an Indirect Reference
One way to VLOOKUP to a different sheet is by using the VLOOKUP function with an indirect reference. This method involves using the INDIRECT function to reference the sheet and range you want to search.
The syntax for this method is:
=VLOOKUP(lookup_value, INDIRECT("'sheet_name'!range"), col_index_num, [range_lookup])
Where:
lookup_valueis the value you want to search forsheet_nameis the name of the sheet you want to searchrangeis the range of cells you want to searchcol_index_numis the column number that contains the value you want to return[range_lookup]is an optional parameter that specifies whether you want an exact match or an approximate match
For example:
=VLOOKUP(A2, INDIRECT("'Sheet2'!A:B"), 2, FALSE)
This formula will search for the value in cell A2 in the range A:B in Sheet2, and return the corresponding value in the second column.
Method 2: Using the INDEX-MATCH Function
Another way to VLOOKUP to a different sheet is by using the INDEX-MATCH function. This method involves using the INDEX function to return a value from a range, and the MATCH function to find the relative position of the value you want to search for.
The syntax for this method is:
=INDEX(range, MATCH(lookup_value, range, [match_type])
Where:
rangeis the range of cells you want to return a value fromlookup_valueis the value you want to search for[match_type]is an optional parameter that specifies whether you want an exact match or an approximate match
For example:
=INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0))
This formula will search for the value in cell A2 in the range A:A in Sheet2, and return the corresponding value in the range B:B in Sheet2.
Method 3: Using the VLOOKUP Function with a Reference to Another Workbook
If you need to VLOOKUP to a different workbook, you can use the VLOOKUP function with a reference to another workbook. This method involves using the VLOOKUP function with a reference to a range in another workbook.
The syntax for this method is:
=VLOOKUP(lookup_value, '[workbook_name]sheet_name'!range, col_index_num, [range_lookup])
Where:
lookup_valueis the value you want to search for[workbook_name]is the name of the workbook you want to searchsheet_nameis the name of the sheet you want to searchrangeis the range of cells you want to searchcol_index_numis the column number that contains the value you want to return[range_lookup]is an optional parameter that specifies whether you want an exact match or an approximate match
For example:
=VLOOKUP(A2, '[Workbook2.xlsx]Sheet1'!A:B, 2, FALSE)
This formula will search for the value in cell A2 in the range A:B in Workbook2.xlsx, and return the corresponding value in the second column.
Method 4: Using the Power Query Function
If you are using Google Sheets or Microsoft Excel 2016 or later, you can use the Power Query function to VLOOKUP to a different sheet or workbook.
The syntax for this method is:
=Power Query(function, parameter)
Where:
functionis the Power Query function you want to useparameteris the parameter you want to pass to the function
For example:
=Power Query(VLOOKUP, A2, Sheet2!A:B, 2, FALSE)
This formula will search for the value in cell A2 in the range A:B in Sheet2, and return the corresponding value in the second column.
Method 5: Using the XLOOKUP Function
If you are using Microsoft Excel 2019 or later, you can use the XLOOKUP function to VLOOKUP to a different sheet or workbook.
The syntax for this method is:
=XLOOKUP(lookup_value, range, col_index_num, [if_not_found], [match_mode], [search_mode])
Where:
lookup_valueis the value you want to search forrangeis the range of cells you want to searchcol_index_numis the column number that contains the value you want to return[if_not_found]is an optional parameter that specifies what to return if the value is not found[match_mode]is an optional parameter that specifies whether you want an exact match or an approximate match[search_mode]is an optional parameter that specifies the search mode
For example:
=XLOOKUP(A2, Sheet2!A:B, 2, "Not Found", FALSE, FALSE)
This formula will search for the value in cell A2 in the range A:B in Sheet2, and return the corresponding value in the second column.
What is the VLOOKUP function?
+The VLOOKUP function is a built-in function in Google Sheets and Microsoft Excel that allows you to search for a value in a table and return a corresponding value from another column.
Can I use the VLOOKUP function to search for a value in a different sheet?
+Yes, you can use the VLOOKUP function to search for a value in a different sheet by using an indirect reference or by using the INDEX-MATCH function.
What is the XLOOKUP function?
+The XLOOKUP function is a built-in function in Microsoft Excel 2019 or later that allows you to search for a value in a table and return a corresponding value from another column.