All About Excel Xlookup

Name: All About Excel Xlookup
Rating: 4.3 (39 reviews)

Short, detailed videos, explaining each aspect of the Excel Xlookup Function

Created bySarah Barnard

Last updated 6/2024

English

English [Auto],

What you'll learn

This Course Is for Excel users, and explains all about the Xlookup Function
Xlookup Explained: Did you know Xlookup can be much quicker to use than Vlookup?
Learn to find data in a table, e.g. for a planet, what is it's size?
Learn how to use Xlookup rather than Hlookup

Course content

4 sections • 14 lectures • 40m total length

Introduction0:55

Create a Range Name2:07
Create a range name by selecting the entire table range, using the name box to assign a single-word name, and saving it for reliable reference in vlookup formulas across sheets.
Amend a Range Name3:28
Highlight the range and create a named range using the name box, then edit or delete it in the formulas name manager, keeping dollar signs intact.
Trick: Convert Text Values to Numbers7:31
Why is it important to be able to convert a text value to a number?
Well, just because from time to time the data that you work with is less than perfect!
Xlookup need values of the same data type, so 123 and "123" may look the same to us, but not for Xlookup.
So to retrieve your data, align the data types first!
The IFError Function2:52
Neaten vlookup formulas by wrapping them in if error to show not found instead of n a, using exact match and table references.

Introduction to Xlookup2:56
Learn how xlookup uses two relevant columns to return a value like a contact title, with exact match (false) and not found handling; contrast with vlookup using a full table.
Xlookup Example1:14
Explore practical xlookup examples to retrieve student marks by name, selecting the lookup range and returning the corresponding physics, chemistry, and maths marks.
Xlookup Can Look Left and Right!1:38
discover how xlookup can search to the left or right of the lookup value and return the corresponding data, such as math records or a city.
What if your data is in multiple lists?2:06
Learn to use xlookup to find values across multiple lists by nesting xlookup to search a second list when not found in the first, and return the corresponding values.
Returning Multiple Values2:33
Xlookup can return multiple values at once from highlighted columns, such as Peter’s biology, maths, and French marks.
Your Value Was Not Found1:30
Match Mode7:06

Requirements

Basic Excel skills, eg you can already enter data into Excel

Description

This course is a collection of short, detailed videos explaining each aspect of the Excel XLOOKUP function.

XLOOKUP is a function introduced in Microsoft Excel that allows you to search for a value in a range or array and return a corresponding value from another range or array. It is a versatile replacement for several older functions, such as VLOOKUP, HLOOKUP, and LOOKUP, providing more flexibility and easier syntax.

To sum up, XLOOKUP is a versatile and efficient function that streamlines lookup tasks in Excel, making it invaluable for anyone working with complex spreadsheets and large volumes of data.

Advantages of XLOOKUP Over VLOOKUP:

No Need for Sorted Data: XLOOKUP can work with unsorted data.
Search in Both Directions: XLOOKUP can search from first to last or last to first.
Supports 2-D Arrays: Unlike VLOOKUP, which can only look up vertically, XLOOKUP can also search horizontally.
Handles Errors Gracefully: The if_not_found parameter allows for more elegant error handling.
Simpler Syntax: No need to specify column index numbers as in VLOOKUP.

Practical Use Cases:

Product Price Lookup: Search for a product’s price using its name or product code.
Employee Data Retrieval: Access employee details like department, salary, or job title.
Data Validation & Lookup: Confirm if a value exists in a dataset and retrieve related information if found.

Who this course is for:

Excel users, Data Analysts

All About Excel Xlookup

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 1min

Prepare data for Xlookup4 lectures • 16min

Vlookups2 lectures • 5min

Xlookup7 lectures • 19min

Requirements

Description

Who this course is for: