If you use Excel 365, use XLOOKUP instead of VLOOKUP. The XLOOKUP function is easier to use and has several advantages.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
How to use the Xlookup function in Excel
1. The XLOOKUP function below finds the value 53 (first parameter) in the range B3:B9 (second parameter).
2. Next, it simply returns the value in the same row from the range E3:E9 (third parameter).
3. Here is another example. Instead of returning the salary, the XLOOKUP function below returns the last name (replace E3:E9 with D3:D9) of ID 79.
Not found
1. For example, the XLOOKUP function below cannot find the value 28 in the range B3:B9.
2. Use the fourth parameter of the XLOOKUP function to replace the #N/A error message.
Approximate match
1. The XLOOKUP function below looks up the value 85 (first parameter) in the range B3:B7 (second parameter). There’s only one problem. There is no value 85 in this range.
2. Fortunately, the value -1 (the fifth parameter) tells the XLOOKUP function to find the next smaller value. In this example the value 80.
3. Next, it simply returns the value in the same row from the range C3:C7 (third parameter).
Search on the left
Instead of using INDEX and MATCH in Excel to do a left lookup, you can simply use the XLOOKUP function. For example, see the XLOOKUP function below.
Returns multiple values
1. First, the XLOOKUP function below looks up the ID and returns the first name (nothing new).
2. Replace C6:C12 with C6:E12 to return first name, last name and salary.
In addition, you can also view many other excel articles here.
