The XLOOKUP, VLOOKUP, and HLOOKUP functions are functions for searching cells.
They can be used in a wide range of applications and are indispensable for using Excel, along with the IF function.
Function Spec.
The XLOOKUP function was added in January 2020.
- This combines the functions of the VLOOKUP and HLOOKUP functions.
- Many inconvenient aspects have been improved.
If you can use the XLOOKUP function, we recommend you to use this function.
XLOOKUP function. Searching for data.
VLOOKUP function. Get the value of a specific column in the row matching the search value.
HLOOKUP function. Get the value of a specific row of a column matching the search value.
Basic Use.
Search a table on a separate sheet from a formula.
Relative cell reference and Absolute cell reference.
Name Manager. Name cell ranges and formulas.
Frequent problems with XLOOKUP/VLOOKUP/HLOOKUP functions and how to deal with them.
Tips.
How to avoid #N/A error in VLOOKUP/HLOOKUP functions.
How to display Hit if the item exists and Miss if it does not exist using the VLOOKUP function.
How to get multiple results with the VLOOKUP function.
How to search from multiple sheets and multiple cell ranges with the VLOOKUP function.
How to avoid VLOOKUP function produces a result of 0.
How to use the VLOOKUP function to efficiently extract multiple columns with copying.
How to shift the result of the VLOOKUP function up or down one line.
How to get the left side value with the VLOOKUP function.
Formula to get the last result with the VLOOKUP function.
How to make the VLOOKUP function case sensitive.
How to select an item from a pull-down list and have it reflected automatically.
How to deal with the VLOOKUP function not displaying dates correctly.
How to use a combination of VLOOKUP and IF functions.
How to get the results of a VLOOKUP function search in cell address.
Formula to get the image with the VLOOKUP function.
How to perform an fuzzy search (forward, partial, or backward match) with the VLOOKUP function.