XLOOKUP function in Excel

XLOOKUP finds a value in one range and returns the matching item from another.

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Argument	Required?	What it is
lookup_value	Required	The value to look for, such as an ID or name.
lookup_array	Required	The row or column to search in.
return_array	Required	The row or column to return a value from. It must be the same size as lookup_array.
if_not_found	Optional	What to return when there is no match, such as "Not found". Without it you get #N/A.
match_mode	Optional	0 for an exact match (the default), -1 or 1 for the next smaller or larger item, 2 for wildcards.
search_mode	Optional	1 searches from the first item (default), -1 from the last.

Type the formula yourself and get it graded instantly, no Excel needed.

XLOOKUP finds a value in one range and returns the matching item from another.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Argument	Required?	What it is
lookup_value	Required	The value to look for, such as an ID or name.
lookup_array	Required	The row or column to search in.
return_array	Required	The row or column to return a value from. It must be the same size as lookup_array.
if_not_found	Optional	What to return when there is no match, such as "Not found". Without it you get #N/A.
match_mode	Optional	0 for an exact match (the default), -1 or 1 for the next smaller or larger item, 2 for wildcards.
search_mode	Optional	1 searches from the first item (default), -1 from the last.

Type the formula yourself and get it graded instantly, no Excel needed.