Exce: Pivot Table with Lookup?

January 7, 2020 6:32 AM

I'm curious if this thing I do in Excel with Pivot tables and an INDEX/MATCH function can be done only with pivot tables.

In this case I am using a pivot table because I like the filtering and grouping functionality. For example I will filter for people age <30, and then group them by Department, Birth year and finally Name, so that I can expand and collapse the various levels. There is also a count column so it shows me the number of people for each level. When I have the Employee name exposed I then have a Lookup function in a neighbouring cell wich shows the ID, name, address whatever from my big table with all the data. I'm not doing any numerical analysis with the displayed data, this is purely for helping me solve a organisational puzzle.

This could also be achieved by setting the filters on a table in the correct sequence, but that's of course not as convenient.

I'm coming back to this puzzle after 2 years and I have to adapt my Workflow to a new environment, and I have time to improve on it. Is there another simpler/better solution for this problem, perhaps only with pivot tables or something?

posted by SweetLiesOfBokonon to Computers & Internet (6 answers total)

I've solved the above with pivot tables, but I've also solved it with a long and complicated macro that adds groupings to rows. The macro is good if you design it to be smart, knowing about groupings and subgroupings, for regular reporting that is unlikely to change. The pivot was better for anything ad-hoc or dynamic or with rows that would change over time.
posted by bbqturtle at 6:39 AM on January 7, 2020

I really hate to not answer your question directly, but just in case you don't get as many answers as you'd like, feel free to Memail me if you're interested in doing this in a programming language like R.
posted by baptismal at 9:01 AM on January 7, 2020

When I have the Employee name exposed I then have a Lookup function in a neighbouring cell wich shows the ID, name, address whatever from my big table with all the data.

I think you can just add the ID and address to the pivot table in the same place you have the name and it should show all three columns.
posted by soelo at 10:56 AM on January 7, 2020

Best answer: You can add any column from your data to the "Row Labels" section after your "Name" column and then go into the "Design" tab on the ribbon and set the Report Layout to Tabular.
posted by willnot at 4:23 PM on January 8, 2020

Do you have Microsoft Access on hand? This could be done using queries and be less prone to error than using filters in Excel.
posted by reenum at 8:09 PM on January 11, 2020

Response by poster: @willnot That's it! Looks kind of ugly but I think I can work with it!
posted by SweetLiesOfBokonon at 1:45 PM on January 14, 2020