Xlookup

Jimbomack

New Member
Joined
Aug 27, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi,



I have a table of data that contains three columns. One column contains a list of 18 names. The other 2 columns contain a date (month/year) and an income value. The entire table consists of these 18 records repeating for each month/year from April2017 to date. Each month, a new set of 18 records is added to the end of the table of data, using the same names, but with different incomes.

I want to pivot the data to create a new table that displays dates down the left-hand side, names across the top, and incomes with the table.

I can use a pivot table however, I need to perform subsequent calculations (division) on this new table set, with another table set of the same size, and I don’t know how (if it’s even possible) to link two pivot tables. Instead of using pivot tables, I find it easier to use two Excel tables that automatically update as data is added.

I have tried to use Xlookup to “pivot” the original table of data into the desired new table format, but I cannot figure out the formula that I assume would use a couple of nested xlookups, one for the date and one for the name.

Any insights would be greatly appreciated.

Thank you,

Jim
table.jpg
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,644
Office Version
  1. 365
Platform
  1. Windows
Lookup functions are nor really suitable for this, something like this should work. As the rows are banded, I've assumed that your source data is in a table, which is much easier for making it dynamic.
Book1 (version 2).xlsb
ABCDEFGH
1Month/YearIncomeNametomharryjane
2Apr-17$ 37,035.00tomApr-17$ 37,035.00$ 38,783.00$ 74,937.00
3Apr-17$ 38,783.00harryMay-17$ 10,746.00$ 80,122.00$ 17,365.00
4Apr-17$ 74,937.00jane
5May-17$ 10,746.00tom
6May-17$ 17,365.00jane
7May-17$ 80,122.00harry
Sheet4
Cell Formulas
RangeFormula
F1:H1F1=TRANSPOSE(UNIQUE(Table1[Name]))
E2:E3E2=UNIQUE(Table1[Month/Year])
F2:H3F2=SUMIFS(Table1[Income],Table1[Month/Year],E2#,Table1[Name],F1#)
Dynamic array formulas.
 

Jimbomack

New Member
Joined
Aug 27, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thank you for your quick response and guidance. My tables are a little more complex than what I provided, (Many additional columns), so I'll need to play with your ideas for a bit, but I think your direction will be very helpful. (Probably your third example will work best) I've spent hours trying to get lookups to work.
Thx again.
Jim
 

Watch MrExcel Video

Forum statistics

Threads
1,129,930
Messages
5,639,051
Members
417,067
Latest member
rohitbabshet

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
Top