Excel XLookup HELP

L

LearnVBA83

Board Regular
Joined
Dec 1, 2016
Messages
113
Office Version
  1. 365
Platform
  1. Windows
Hi there! I Know i could use a sumif or sumifs function, but i'm trying to learn the ins and outs of the Xlookup and how it spills. In the below scenario (mini-sheet). How would i use an Xlookup to find/spill all of the values for January Sales? When i try the Xlookup it only finds the first value. I would ultimately want to sum the return of all January Sales. I've tried several variations such as this =SUM(XLOOKUP($A$2,$A$2:$A$193,F2:F193)) but it seems like it only wants to return the first match. I was under the impression the XLookup would find all of the January Sales matches. Any help would be great!


Master Excel Guide.xlsm
ABCDEF
1MonthYearRegionProductDescriptionSales
2January2021NorthProduct AHead-Ache166,281
3February2021NorthProduct AHead-Ache77,907
4March2021NorthProduct AHead-Ache155,568
5April2021NorthProduct AHead-Ache154,626
6May2021NorthProduct AHead-Ache57,510
7June2021NorthProduct AHead-Ache70,559
8July2021NorthProduct AHead-Ache127,324
9August2021NorthProduct AHead-Ache57,560
10September2021NorthProduct AHead-Ache57,738
11October2021NorthProduct AHead-Ache121,767
12November2021NorthProduct AHead-Ache71,101
13December2021NorthProduct AHead-Ache79,371
14January2022NorthProduct AHead-Ache96,792
15February2022NorthProduct AHead-Ache108,522
16March2022NorthProduct AHead-Ache145,017
17April2022NorthProduct AHead-Ache80,597
18May2022NorthProduct AHead-Ache170,889
19June2022NorthProduct AHead-Ache116,532
20July2022NorthProduct AHead-Ache147,794
21August2022NorthProduct AHead-Ache56,897
22September2022NorthProduct AHead-Ache63,169
23October2022NorthProduct AHead-Ache143,560
24November2022NorthProduct AHead-Ache109,524
25December2022NorthProduct AHead-Ache136,862
26January2021NorthProduct BFlu86,761
27February2021NorthProduct BFlu155,546
28March2021NorthProduct BFlu65,236
29April2021NorthProduct BFlu75,271
30May2021NorthProduct BFlu160,705
31June2021NorthProduct BFlu82,710
32July2021NorthProduct BFlu155,690
33August2021NorthProduct BFlu123,234
34September2021NorthProduct BFlu149,884
35October2021NorthProduct BFlu94,163
36November2021NorthProduct BFlu113,598
37December2021NorthProduct BFlu54,835
38January2022NorthProduct BFlu80,440
39February2022NorthProduct BFlu168,269
40March2022NorthProduct BFlu60,338
41April2022NorthProduct BFlu52,986
42May2022NorthProduct BFlu150,478
43June2022NorthProduct BFlu66,450
44July2022NorthProduct BFlu159,796
45August2022NorthProduct BFlu54,295
46September2022NorthProduct BFlu153,412
47October2022NorthProduct BFlu150,478
48November2022NorthProduct BFlu61,865
49December2022NorthProduct BFlu131,054
50January2021SouthProduct AHead-Ache102,921
51February2021SouthProduct AHead-Ache105,693
52March2021SouthProduct AHead-Ache120,843
53April2021SouthProduct AHead-Ache168,840
54May2021SouthProduct AHead-Ache155,087
55June2021SouthProduct AHead-Ache136,161
56July2021SouthProduct AHead-Ache152,025
57August2021SouthProduct AHead-Ache169,731
58September2021SouthProduct AHead-Ache78,028
59October2021SouthProduct AHead-Ache101,865
60November2021SouthProduct AHead-Ache90,575
61December2021SouthProduct AHead-Ache149,291
62January2022SouthProduct AHead-Ache132,235
63February2022SouthProduct AHead-Ache108,322
64March2022SouthProduct AHead-Ache160,340
65April2022SouthProduct AHead-Ache113,402
66May2022SouthProduct AHead-Ache109,279
67June2022SouthProduct AHead-Ache125,424
68July2022SouthProduct AHead-Ache107,286
69August2022SouthProduct AHead-Ache68,295
70September2022SouthProduct AHead-Ache172,298
71October2022SouthProduct AHead-Ache151,136
72November2022SouthProduct AHead-Ache141,437
73December2022SouthProduct AHead-Ache146,143
74January2021SouthProduct BFlu160,801
75February2021SouthProduct BFlu107,032
76March2021SouthProduct BFlu66,950
77April2021SouthProduct BFlu55,010
78May2021SouthProduct BFlu99,000
79June2021SouthProduct BFlu92,651
80July2021SouthProduct BFlu129,894
81August2021SouthProduct BFlu89,232
82September2021SouthProduct BFlu105,532
83October2021SouthProduct BFlu128,118
84November2021SouthProduct BFlu90,636
85December2021SouthProduct BFlu81,674
86January2022SouthProduct BFlu104,677
87February2022SouthProduct BFlu88,355
88March2022SouthProduct BFlu153,830
89April2022SouthProduct BFlu81,710
90May2022SouthProduct BFlu83,990
91June2022SouthProduct BFlu153,786
92July2022SouthProduct BFlu128,677
93August2022SouthProduct BFlu53,077
94September2022SouthProduct BFlu146,294
95October2022SouthProduct BFlu149,081
96November2022SouthProduct BFlu112,475
97December2022SouthProduct BFlu169,096
98January2021EastProduct AHead-Ache113,118
99February2021EastProduct AHead-Ache53,798
100March2021EastProduct AHead-Ache155,301
101April2021EastProduct AHead-Ache66,394
102May2021EastProduct AHead-Ache78,984
103June2021EastProduct AHead-Ache125,353
104July2021EastProduct AHead-Ache75,520
105August2021EastProduct AHead-Ache97,129
106September2021EastProduct AHead-Ache103,448
107October2021EastProduct AHead-Ache138,493
108November2021EastProduct AHead-Ache82,690
109December2021EastProduct AHead-Ache162,388
110January2022EastProduct AHead-Ache135,373
111February2022EastProduct AHead-Ache162,930
112March2022EastProduct AHead-Ache153,652
113April2022EastProduct AHead-Ache142,866
114May2022EastProduct AHead-Ache158,847
115June2022EastProduct AHead-Ache139,711
116July2022EastProduct AHead-Ache93,048
117August2022EastProduct AHead-Ache51,312
118September2022EastProduct AHead-Ache74,037
119October2022EastProduct AHead-Ache137,203
120November2022EastProduct AHead-Ache165,677
121December2022EastProduct AHead-Ache105,220
122January2021EastProduct BFlu110,730
123February2021EastProduct BFlu68,913
124March2021EastProduct BFlu112,824
125April2021EastProduct BFlu70,222
126May2021EastProduct BFlu133,682
127June2021EastProduct BFlu125,098
128July2021EastProduct BFlu64,604
129August2021EastProduct BFlu91,627
130September2021EastProduct BFlu155,395
131October2021EastProduct BFlu108,555
132November2021EastProduct BFlu81,056
133December2021EastProduct BFlu130,860
134January2022EastProduct BFlu65,310
135February2022EastProduct BFlu163,621
136March2022EastProduct BFlu94,486
137April2022EastProduct BFlu163,609
138May2022EastProduct BFlu143,568
139June2022EastProduct BFlu57,907
140July2022EastProduct BFlu170,860
141August2022EastProduct BFlu117,044
142September2022EastProduct BFlu112,059
143October2022EastProduct BFlu94,271
144November2022EastProduct BFlu136,888
145December2022EastProduct BFlu88,185
146January2021WestProduct AHead-Ache144,117
147February2021WestProduct AHead-Ache60,120
148March2021WestProduct AHead-Ache116,686
149April2021WestProduct AHead-Ache132,902
150May2021WestProduct AHead-Ache76,634
151June2021WestProduct AHead-Ache113,426
152July2021WestProduct AHead-Ache141,648
153August2021WestProduct AHead-Ache54,381
154September2021WestProduct AHead-Ache122,487
155October2021WestProduct AHead-Ache77,884
156November2021WestProduct AHead-Ache135,615
157December2021WestProduct AHead-Ache151,991
158January2022WestProduct AHead-Ache106,806
159February2022WestProduct AHead-Ache134,956
160March2022WestProduct AHead-Ache130,266
161April2022WestProduct AHead-Ache122,902
162May2022WestProduct AHead-Ache111,141
163June2022WestProduct AHead-Ache64,740
164July2022WestProduct AHead-Ache158,469
165August2022WestProduct AHead-Ache131,864
166September2022WestProduct AHead-Ache145,960
167October2022WestProduct AHead-Ache82,168
168November2022WestProduct AHead-Ache106,573
169December2022WestProduct AHead-Ache71,809
170January2021WestProduct BFlu91,532
171February2021WestProduct BFlu107,585
172March2021WestProduct BFlu165,283
173April2021WestProduct BFlu99,878
174May2021WestProduct BFlu103,766
175June2021WestProduct BFlu96,665
176July2021WestProduct BFlu167,201
177August2021WestProduct BFlu147,167
178September2021WestProduct BFlu52,725
179October2021WestProduct BFlu84,977
180November2021WestProduct BFlu93,892
181December2021WestProduct BFlu100,081
182January2022WestProduct BFlu75,418
183February2022WestProduct BFlu91,153
184March2022WestProduct BFlu90,387
185April2022WestProduct BFlu95,073
186May2022WestProduct BFlu111,194
187June2022WestProduct BFlu132,745
188July2022WestProduct BFlu127,923
189August2022WestProduct BFlu54,733
190September2022WestProduct BFlu129,644
191October2022WestProduct BFlu100,368
192November2022WestProduct BFlu110,829
193December2022WestProduct BFlu125,074
Data
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
I don't think xlLookUp can do what you want.

XLOOKUP function - Microsoft Support

Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from...
support.microsoft.com support.microsoft.com
 
Upvote 0
i'm not sure xlookup is the best way to go. FILTER and the sum of products (used above ) i think are easier. Filter will work if you have 365 or excel 2021.
 
Upvote 0
awoohaw said:
i'm not sure xlookup is the best way to go. FILTER and the sum of products (used above ) i think are easier. Filter will work if you have 365 or excel 2021.
Click to expand...
You will need to change the SUM to SUMPRODUCT if you are not using MS365 ;)
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

LearnVBA83 said:
I was under the impression the XLookup would find all of the January Sales matches.
Click to expand...
Nope, xlookup is the same as vlookup & match, in that it will only return the 1st match.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Macro to add sum total to track percentages
Replies
0
Views
339
spenserallen7
S
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
426
STARL0RD
S
S
  • Question Question
Dynamic Row Reference
Replies
1
Views
495
KRice
K
Jyggalag
  • Solved
Conditional formatting for blank cells containing formulas?
Replies
8
Views
5K
AgingHippie
A
M
  • Solved
Nested Xlookup not returning all data
Replies
2
Views
469
Mat1968
M

Forum statistics

Threads
1,224,815
Messages
6,181,135
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top