Xlookup

[Jimbomack]

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

 

[jasonb75]

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
	A	B	C	D	E	F	G	H
1	Month/Year	Income	Name			tom	harry	jane
2	Apr-17	$ 37,035.00	tom		Apr-17	$ 37,035.00	$ 38,783.00	$ 74,937.00
3	Apr-17	$ 38,783.00	harry		May-17	$ 10,746.00	$ 80,122.00	$ 17,365.00
4	Apr-17	$ 74,937.00	jane
5	May-17	$ 10,746.00	tom
6	May-17	$ 17,365.00	jane
7	May-17	$ 80,122.00	harry
Sheet4

Cell Formulas
Range	Formula
F1:H1	F1	=TRANSPOSE(UNIQUE(Table1[Name]))
E2:E3	E2	=UNIQUE(Table1[Month/Year])
F2:H3	F2	=SUMIFS(Table1[Income],Table1[Month/Year],E2#,Table1[Name],F1#)
Dynamic array formulas.

 

[Jimbomack]

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