XLOOKUP Function click here to read tutorial

Note: the formulas in this file require Office 365.

Syntax: =XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [

Argument Description
lookup_value The value you want to find, or cell containing the item you want to find
lookup_array The cell range or array you want to search
return_array The cell range or array containing the value you want returned
[if_not_found] Optional - the text you want returned in the event a match isn't found. If omitted a
be returned.
[match_mode] Optional - Defaults to 0 for exact match

[search_mode] Optional - Defaults to 1 searching first to last

Options 2 and -2 require the lookup_array to be sorted in ascending or descending

*Binary search does not result in faster calculations now that Microsoft have optimised the lookup alg

Examples

1. Simple XLOOKUP
2. XLOOKUP does HLOOKUP
3. XLOOKUP does INDEX & MATCH
4. XLOOKUP Returns Multiple Columns
5. XLOOKUP Dynamic Range
6. XLOOKUP Error Handling
7. XLOOKUP Last Value
8. XLOOKUP Left
9. XLOOKUP Wildcards
10. XLOOKUP Approximate Match
More Resources
 click here to read tutorial

ay, [if_not_found], [match_mode], [search_mode])

item you want to find

want returned
t a match isn't found. If omitted an error will

orted in ascending or descending order respectively.*

oft have optimised the lookup algorithms.
Category Product Sales Average Rating Product Sales
Accessories Bike Racks 64,400 94% Caps 23,600
Accessories Helmets 36,400 65%
Accessories Lights 36,700 90%
The lookup_array and return_array
Accessories Locks 35,000 100% must contain the same number of
Bikes Cargo Bike 13,000 54% rows.
Bikes Mountain Bikes 8,500 46%
Clothing Bib-Shorts 10,200 30%
Clothing Caps 23,600 58%
Clothing Gloves 90,700 89%
Clothing Jerseys 7,500 40%
Components Bottom Brackets 17,500 46%
Components Brakes 8,500 40%
Components Chains 56,000 88%
Components Handlebars 8,100 29%
 Products DV List
=_xlfn.xlookup(F2,B2:B15,C2:C15) #NAME?
#NAME?
#NAME?
The lookup_array and return_array
must contain the same number of #NAME?
rows. #NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov
64,400 36,400 36,700 35,000 13,000 8,500 10,200 23,600 90,700 7,500 17,500

Oct 7,500 =_xlfn.xlookup(B5,A1:L1,A2:L2)

A vertical lookup_array must contain the same number of

rows as the return_array and a horizontal lookup_array, as
in this example, must contain the same number of columns
as the return_array.
Dec
8,500
Month Bikes Clothing Accessories
Jan 64,400 35,000 21,800 Clothing Feb #NAME?
Feb 36,400 8,500 34,000 8,500
Mar 36,700 6,200 32,000 #NAME?
Apr 35,000 30,700 9,700
May 13,000 16,900 36,400
Jun 8,500 3,100 10,200
Jul 10,200 23,000 7,500
Aug 23,600 3,700 36,000
Sep 90,700 36,000 17,500
Oct 7,500 63,700 30,700
Nov 17,500 3,100 35,000
Dec 8,500 2,400 23,600
{=_xlfn.xlookup(G2,A2:A13,_xlfn.xlookup(F2,B1:D1,B2:D13))}
=INDEX(B2:D13,MATCH(G2,A2:A13,0),MATCH(F2,B1:D1,0))
Alternatives to XLOOKUP
{=INDEX(B2:D13,_xlfn.xmatch(G2,A2:A13),_xlfn.xmatch(F2,B1:D1))}
Category Product Sales Average Rating Product Sales Avg. Rating
Accessories Bike Racks 64,400 94% Helmets #NAME? #NAME?
Accessories Helmets 36,400 65%
Accessories Lights 36,700 90% return_array
Accessories Locks 35,000 100%
Bikes Cargo Bike 13,000 54% Product Sales Category
Bikes Mountain Bikes 8,500 46% Helmets #NAME? #NAME?
Clothing Bib-Shorts 10,200 30%
Clothing Caps 23,600 58% Use CHOOSE in the
Clothing Gloves 90,700 89%
Clothing Jerseys 7,500 40% Product Sales Avg. Rating
Components Bottom Brackets 17,500 46% #NAME? #NAME?
Components Brakes 8,500 40% #NAME? #NAME?
Components Chains 56,000 88% #NAME? #NAME?
Components Handlebars 8,100 29% #NAME? #NAME?

Product Sales Avg. Rating

#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?

Product Sales Avg. Rating

#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
 Formula Products DV List
{=_xlfn.xlookup(F2,B2:B15,C2:D15)} #NAME?
#NAME?
return_array contains two results; columns C and D. #NAME?
#NAME?
#NAME?
{=_xlfn.xlookup(F7,B2:B15,CHOOSE({1,2},C2:C15,A2:A15))} #NAME?
#NAME?
Use CHOOSE in the return_array to specify non-contiguous #NAME?
columns #NAME?
#NAME?
=XLOOKUP(F12#,B2:B15,C2:D15) #NAME?
#NAME?
XLOOKUP will only spill the first multi-value array, in #NAME?
this case the lookup_value array F9#, and ignores #NAME?
the multiple column result in the return_array. This
is a limitation of the Excel calc engine.

One solution is to use separate XLOOKUP

formulas for each column.

=XLOOKUP(F27,$B$2:$B$15,C2:D15)

Or only reference one cell in the lookup_value

and then copy the formula down.
Date Category Product Sales Rating
1/1/2018 Clothing Bib-Shorts 4,000 22% Start Date End Date
2/1/2018 Accessories Bike Racks 33,700 92% 7/1/2018 12/1/2018
3/1/2018 Components Bottom Brackets 600 27%
4/1/2018 Components Brakes 5,400 38%
5/1/2018 Clothing Caps 600 15%
6/1/2018 Bikes Cargo Bike 9,300 60%
7/1/2018 Components Chains 20,000 75%
8/1/2018 Clothing Gloves 27,000 88%
9/1/2018 Components Handlebars 5,000 35%
10/1/2018 Accessories Helmets 34,000 95%
11/1/2018 Clothing Jerseys 7,500 40%
First XLOOKUP result
12/1/2018 Accessories Lights 36,700 90%
1/1/2019 Accessories Locks 35,000 100%
2/1/2019 Bikes Mountain Bikes 8,500 46%
3/1/2019 Clothing Bib-Shorts 6,200 38%
4/1/2019 Accessories Bike Racks 30,700 95%
5/1/2019 Components Bottom Brackets 16,900 65%
6/1/2019 Components Brakes 3,100 42%
7/1/2019 Clothing Caps 23,000 100%
8/1/2019 Bikes Cargo Bike 3,700 48%
9/1/2019 Components Chains 36,000 100%
10/1/2019 Clothing Gloves 63,700 90%
11/1/2019 Components Handlebars 3,100 22%
12/1/2019 Accessories Helmets 2,400 35%
1/1/2020 Clothing Jerseys 21,800 96%
2/1/2020 Accessories Lights 34,000 89%
3/1/2020 Accessories Locks 32,000 97%
4/1/2020 Bikes Mountain Bikes 9,700 48%
 Sales
$ 130,200 =SUM( _xlfn.xlookup(G3,A2:A29,D2:D29) : _xlfn.xlookup(H3,A2:A29,D2:D29) )

Second XLOOKUP result

First XLOOKUP result

Category Product Sales Average Rating Locks Not Found
Accessories Bike Racks 64,400 94%
Accessories Helmets 36,400 65%
Accessories Lights 36,700 90%
Bikes Cargo Bike 13,000 54%
Bikes Mountain Bikes 8,500 46%
Clothing Bib-Shorts 10,200 30%
Clothing Caps 23,600 58%
Clothing Gloves 90,700 89%
Clothing Jerseys 7,500 40%
Components Bottom Brackets 17,500 46%
Components Brakes 8,500 40%
Components Chains 56,000 88%
Components Handlebars 8,100 29%
=_xlfn.xlookup(F1,B2:B14,C2:C14,"Not Found") Products DV List
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
Category Product Sales Average Rating Find the last Sales value for Clothing
Accessories Bike Racks 64,400 94% Clothing 7,500
Accessories Helmets 36,400 65%
Accessories Lights 36,700 90%
Accessories Locks 35,000 100%
Bikes Cargo Bike 13,000 54%
Bikes Mountain Bikes 8,500 46%
Clothing Bib-Shorts 10,200 30%
Clothing Caps 23,600 58%
Clothing Gloves 90,700 89%
Clothing Jerseys 7,500 40%
Components Bottom Brackets 17,500 46%
Components Brakes 8,500 40%
Components Chains 56,000 88%
Components Handlebars 8,100 29%
Sales value for Clothing Category DV List
=_xlfn.xlookup(F2,A2:A15,C2:C15,,0,-1) #NAME?
#NAME?
#NAME?
#NAME?
Product Sales Rating Category Category Product
Bike Racks 64,400 94% Accessories Accessories Bike Racks
Helmets 36,400 65% Accessories Accessories Helmets
Lights 36,700 90% Accessories Accessories Lights
Locks 35,000 100% Accessories Accessories Locks
Cargo Bike 13,000 54% Bikes Bikes Cargo Bike
Mountain Bikes 8,500 46% Bikes Bikes Mountain Bikes
Bib-Shorts 10,200 30% Clothing Clothing Bib-Shorts
Caps 23,600 58% Clothing Clothing Caps
Gloves 90,700 89% Clothing Clothing Gloves
Jerseys 7,500 40% Clothing Clothing Jerseys
Bottom Brackets 17,500 46% Components Components Bottom Brackets
Brakes 8,500 40% Components Components Brakes
Chains 56,000 88% Components Components Chains
Handlebars 8,100 29% Components Components Handlebars
Pre Office 365 versions; force VLOOKUP to look left with CHOOSE
Category Product Sales Average Rating *Bike* 64,400
Accessories Bike Racks 64,400 94%
Accessories Helmets 36,400 65%
Accessories Lights 36,700 90%
Accessories Locks 35,000 100%
Bikes Cargo Bike 13,000 54%
Bikes Mountain Bikes 8,500 46%
Clothing Bib-Shorts 10,200 30%
Clothing Caps 23,600 58%
Clothing Gloves 90,700 89%
Clothing Jerseys 7,500 40%
Components Bottom Brackets 17,500 46%
Components Brakes 8,500 40%
Components Chains 56,000 88%
Components Handlebars 8,100 29%
=_xlfn.xlookup(F1,B2:B15,C2:C15,,2)
Salesperson Sales Commission Sales Commission
Burton 50,500 25% 10,000 5%
Unlike VLOOKUP, XLOOKUP
Donaldson 36,400 #NAME? 20,000 10% doesn't require the sales bands
Regan 10,200 #NAME? 30,000 15% to be in a sorted list!
Carter 23,600 #NAME? 40,000 20%
Albert 13,000 #NAME? 50,000 25%
Peters 8,500 #NAME? 0 0%
P, XLOOKUP
the sales bands
d list!
More Resources
Tutorials
Excel Functions.............................................................
Charting Blog Posts.......................................................
Excel Dashboard Blog Posts..........................................

Webinars
Excel Dashboards & Power BI.......................................

Courses
Advanced Excel.............................................................
Advanced Excel Formulas.............................................
Power Query.................................................................
PivotTable Quick Start..................................................
Xtreme PivotTables.......................................................
Power Pivot...................................................................
Excel Dashboards..........................................................
Power BI.......................................................................
Excel for Decision Making Under Uncertainty..............
Excel for Finance Professionals.....................................
Excel Analysis ToolPak..................................................
Excel for Customer Service Professionals.....................
Excel for Operations Management...............................
Financial Modelling.......................................................

Support
Excel Forum..................................................................
https://www.myonlinetraininghub.com/excel-functions
http://www.myonlinetraininghub.com/category/excel-charts
http://www.myonlinetraininghub.com/category/excel-dashboard

http://www.myonlinetraininghub.com/excel-webinars

https://www.myonlinetraininghub.com/excel-expert-upgrade
https://www.myonlinetraininghub.com/advanced-excel-formulas-course
https://www.myonlinetraininghub.com/excel-power-query-course
https://www.myonlinetraininghub.com/excel-pivottable-course-quick-start
https://www.myonlinetraininghub.com/excel-pivottable-course
https://www.myonlinetraininghub.com/power-pivot-course
http://www.myonlinetraininghub.com/excel-dashboard-course
http://www.myonlinetraininghub.com/power-bi-course
https://www.myonlinetraininghub.com/excel-for-decision-making-course
https://www.myonlinetraininghub.com/excel-for-finance-course
https://www.myonlinetraininghub.com/excel-analysis-toolpak-course
https://www.myonlinetraininghub.com/excel-for-customer-service-professionals
https://www.myonlinetraininghub.com/excel-operations-management-course
https://www.myonlinetraininghub.com/financial-modelling-course

https://www.myonlinetraininghub.com/excel-forum