How to search from multiple sheets and multiple cell ranges with the VLOOKUP function.(Microsoft Excel)
The VLOOKUP function is originally a function to specify a single cell range as a search target and obtain results.
Therefore, it is not possible to specify multiple sheets or ranges of cells as the search target in normal usage.
This section introduces an application method for specifying multiple sheets and ranges of cells.
The XLOOKUP function can also be used in the same way.
Steps.
The procedure is described in this example where the search range is divided into three tables.
If there are multiple matches for the search value, the first match will be retrieved if used as is due to the specifications of the VLOOKUP and XLOOKUP functions.
VSTACK and HSTACK functions combine search targets
This function cannot be used with older versions of Excel because it uses a new function that was added to Microsoft365 (Office365) Excel in September 2022.
The VSTACK and HSTACK functions join a range of cells and the results can be used in the VLOOKUP and XLOOKUP functions.
=VSTACK(Fishes!A2:B4,Meats!A2:B4,Fruits!A2:B4)
When using the XLOOKUP function, the search and return ranges must be combined.
=XLOOKUP(C2, VSTACK(Fishes!A2:A4,Meats!A2:A4,Fruits!A2:A4), VSTACK(Fishes!B2:B4,Meats!B2:B4,Fruits!B2:B4) )
Example of use for XLOOKUP function.
When you want to specify an entire column in a formula
There are cases where the number of rows to be searched is indefinite and you want to specify the column itself.
However, this would result in an error because the number of rows exceeds the limit of the number of rows in Excel.
In this case, you need to restrict yourself to only valid rows before merging.
You may use the formula for only valid rows as is, but it will be easier to use if you register the formula in Name Management.
Specify any function name in "Name" and the following formula in "Refers to".
=LAMBDA(SpecifyCell,LET(CalCell,HSTACK(BYROW(SpecifyCell,LAMBDA(r,COUNTA(r))),SpecifyCell),MAP(DROP(FILTER(CalCell,CHOOSECOLS(CalCell,1)>0),,1),LAMBDA(Cell,IF(Cell="","",Cell)))))
It will be available with the specified function name. (“EnableRows” in the example)
If header rows exist, DROP removes the number of rows for the header.
This cell range can be used directly for the VLOOKUP function.
=VLOOKUP(C2, VSTACK(DROP(EnableRows(Fishes!A:B),1),DROP(EnableRows(Meats!A:B),1),DROP(EnableRows(Fruits!A:B),1)),2 )
It can be used for the XLOOKUP function, but it is used in conjunction with the CHOOSECOLS function, which extracts columns from a range.
=XLOOKUP(C2,CHOOSECOLS( VSTACK(DROP(EnableRows(Fishes!A:B),1),DROP(EnableRows(Meats!A:B),1),DROP(EnableRows(Fruits!A:B),1)),1),CHOOSECOLS( VSTACK(DROP(EnableRows(Fishes!A:B),1),DROP(EnableRows(Meats!A:B),1),DROP(EnableRows(Fruits!A:B),1)),2))
How to specify a search target
Specify a sheet (cell range) with a cell.
Use the INDIRECT function to specify a range of functions.
=VLOOKUP(C2,INDIRECT(C4&"!A:B"),2,FALSE)
=XLOOKUP(C2,INDIRECT(C4&"!A:A"),INDIRECT(C4&"!B:B"))
How to search in order without specifying the search target
VLOOKUP function and IFERROR function are used and set in order.
If you specify search values that exist in multiple search targets, they are given priority in the order written (in the example, Fishes -> Meats -> Fruits).
=IFERROR(VLOOKUP(C2,Fishes!A:B,2,FALSE),IFERROR(VLOOKUP(C2,Meats!A:B,2,FALSE),VLOOKUP(C2,Fruits!A:B,2,FALSE)))
=IFERROR(XLOOKUP(C2,Fishes!A:A,Fishes!B:B),IFERROR(XLOOKUP(C2,Meats!A:A,Meats!B:B),XLOOKUP(C2,Fruits!A:A,Fruits!B:B)))
---