Looking for help with xLOOKUP Return All Matches

twstevens

New Member
Joined
Dec 10, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I want to use the xlookup to find a transaction in a check register and put that transaction in a budget report. I am able to get xlookup to find a single line item for a budget category but when a category has multiple entries xlookup is only returning the first line item of that category. What I am after is not only to find the first transaction of a particular category but to find all of them and add(sum) them all together into one cell. ex: find all amounts paid for groceries then sum them all together.

I am using the date & category as a reference to find the amount paid in each category. Here is the formula I am using it only finds the first transaction. How do I need to change it to find all transactions and sum them all together in one cell?

=IFERROR(XLOOKUP($J$4&F15,'Arvest Savings'!$C:$C&'Arvest Savings'!$E:$E,'Arvest Savings'!$H:$H,,0),"")

The above formula is going into the budget form under the actual column then once the entry is made in the check register it automatically populates into the budget form for the appropriate month,

It works great for a single transaction where I need assistance is with finding multiple transactions and getting them summed all together.


I have a separate worksheet that has the check register data on it.

Check #DatePayeeCategoryPaymentClrDepositBalance

I can get xlookup to work to find only the first amount I pay for groceries what I need is to have xlookup to find all amounts paid for groceries and sum them into one cell on a budget sheet. Like this one under the actual column.

ExpensesActualBudgetStatus
Amazon (General Merchandise)
Amazon Music HD Subscription
Amazon Prime Subscription
Clothing Expense
Computer Hardware
Computer Software
Cox Internet Service
Delivery Service Walmart Plus
Dentist
Dining Out
Disney Plus Subscription
Doctor
Eye Care
Facebook Games
Groceries (Walmart)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi & welcome to MrExcel.
How about
Excel Formula:
=SUMIFS('Arvest Savings'!$H:$H,'Arvest Savings'!$C:$C,$J$4,'Arvest Savings'!$E:$E,F15)
 
Upvote 0
Hi & welcome to MrExcel.
How about
Excel Formula:
=SUMIFS('Arvest Savings'!$H:$H,'Arvest Savings'!$C:$C,$J$4,'Arvest Savings'!$E:$E,F15)

The above formula worked I was able to get the results I needed. I have also altered the formula to use in other parts of my budget workbook to resolve other calculation issues I was having so I now have a fully functioning budget workbook to track my budget.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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