What type of look up? Xlookup with multiple criteria?

chloev

New Member
Joined
Jan 27, 2016
Messages
22
Hello!

I am trying to create a summary report for my data as my pivot tables are becoming too huge.

My source data looks something like this (in a separate sheet about 450 rows):

NameStudyrequestedassessedapproved
joe blogsstudy 1
01-Jan-20​
02-Jan-20​
03-Jan-20​
joanne smithstudy 1
01-Feb-20​
02-Feb-20​
joe blogsstudy 2
01-Jan-20​
02-Jan-20​
joanne smithstudy 2
01-Jan-20​
joe blogsstudy 3
01-Jan-20​
joanne smithstudy 3
01-Jan-20​
02-Jan-20​
03-Jan-20​

And I would like my summary table to look like this:
Namestudy 1study 2study 3
joe blogsapprovedassessedrequested
joanne smithassessedrequestedapproved


At the moment i have a pivot table for each study which just shows a count for each column- the team i send the report to are ok with it but its just becoming so huge and i cannot fit a single study on a slide.
Ideally i would like a status per study for each person? I thought i could perhaps use the pivot of the source ? that might make things more simple, but if im honest i have no idea how to do it and have watched so many youtube videos that are not quite right.

any ideas on how to do this?

Very grateful for your support!
Thanks!

C
 

Attachments

  • source data.png
    source data.png
    16 KB · Views: 3

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Also, perhaps relevant,
Each person does not have a row for every study in the source data-some people are not taking part in every study.
To make the summary table I pulled the list of names and removed duplicates and added study names as column headers.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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