The XLOOKUP function
As you will see, XLOOKUP not only eliminates the frustrations I mentioned for VLOOKUP, it enables you to create a simpler formula than INDEX/MATCH and adds much more functionality. The =XLOOKUP($G3,$B3:$B12,D3:D12) formula in cell H3 of the XLOOKUP Exact Match worksheet in Figure 10.6 returns 94 as the length of Ninety Mile Beach in miles. As with VLOOKUP, XLOOKUP stops looking after it finds an initial match:
Figure 10.6 – The XLOOKUP function
Tip
XLOOKUP eliminates most, but not all, of the previous uses for INDEX/MATCH. XLOOKUP doesn’t allow you to simultaneously search down a column and across a row like INDEX with two MATCH functions allows, so it’s good to have both approaches in your repertoire.
XLOOKUP has a total of six arguments, but often, you’ll only need to enter the three required arguments:
- Lookup_value – A value that you’re searching for, such as the contents of cell...
