XLOOKUP is a new and exciting function that replaces VLOOKUP and addresses most of the limitations of its predecessor. XLOOKUP is so versatile that it also replaces HLOOKUP and the INDEX/MATCH/MATCH combination used for simultaneous vertical and horizontal lookups.
Author(s): George, Nathan
Year: 2020
Language: English
Pages: 122
Tags: excel functions lookup formulas xlookup
Title page
Copyright
Introduction
Who Is This Book For?
Assumptions
Practice Files
Chapter 1: How to Enter a Formula
The Insert Function Dialog Box
Chapter 2: XLOOKUP Function
Key Features and Differences With VLOOKUP
Syntax and Arguments
XLOOKUP Examples
Vertical Lookup
Horizontal Lookup
Simultaneous Vertical and Horizontal Lookup
Return Multiple Values with Horizontal Spill
Return Multiple Values with Vertical Spill
Return Multiple Values from Non-Contiguous Ranges
Dynamic Range with XLOOKUP
Handling #N/A Errors
Find the Last Value First
XLOOKUP Left
Using Wildcards with XLOOKUP
Finding an Approximate Match
Common XLOOKUP Errors and Solutions
Chapter 3: VLOOKUP Function
Syntax and Arguments
Standard VLOOKUP Example
Finding an Approximate Match with VLOOKUP
Using VLOOKUP and the CHOOSE Function for Left Lookups
VLOOKUP and Error Handling
VLOOKUP and Wildcards
Best Practices for VLOOKUP
Common Errors and Solutions
Chapter 4: Using INDEX and XMATCH for Lookups
XMATCH Function
XMATCH Examples
INDEX Function - Array Form
INDEX Example
Using INDEX/XMATCH/XMATCH for simultaneous vertical and horizontal lookups
Chapter 5: FILTER Function
Sample Data for Examples
How to Create a Drop-down List
Simple FILTER Example
Multiple Criteria Filter using AND
Multiple Criteria Filter using OR
Sorting Filtered Data
Chapter 6: Error Handling Functions
IFERROR Function
IFNA Function
The difference between IFERROR and IFNA
Chapter 7: How to Step Through a Formula
Appendix: Shortcut Keys for Working with Data, Functions, and the Formula Bar
Glossary
Afterword
Please Leave a Review
More on Functions
Other Books by Author
