XLookup help

SrCM

New Member
Joined
Aug 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello!

I am desperate for some X lookup help. I have been trying to figure this out for over a week now and either this is not feasible or I am overlooking something.

I have a registration data sheet that has child name with information for each row, so since some families are registering multiple children the parent information is duplicated. What I would like to do is extract the data to another sheet that has a summary of registrations per family listing the parent name on the column and across the row each child and the day/time they are registered. I've tried a lot different stuff but nada. Please help I've included a screen shot of the original data as well as the info I'm trying to pull.

In the family registration sheet I first sorted out the family name using the UNIQUE formula, then I tried XLOOKUP for the second one and I've tried multiple combinations and still I'm coming up with more frustration than anything else. I need to find a quicker way to sort this out because I will be adding more registrations and it would be helpful to not have to go back to this every time so that I can give the families as well as the coordinator this information.
REgistration data.JPG
Registration by family.JPG


Thanks for any help you can provide
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,137
Office Version
  1. 365
  2. 2010
Welcome to Mr. Excel,

It would greatly help someone trying to help you if you posted your data using XL2BB so that retyping the info isn't required.
 

SrCM

New Member
Joined
Aug 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Unfortunately I am unable to use XL2BB on the computer that I am using...
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,773
Office Version
  1. 365
Platform
  1. Windows
Unfortunately I am unable to use XL2BB on the computer that I am using...
An alternative is to make up a small but representative sample and copy/paste directly in your post here.
Make clear what the columns & rows that you have posted

For example, here is B2:D3


TomNo
3​
JimYes
5​

Do the same thing for the expected results.
 

SrCM

New Member
Joined
Aug 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Formula in Cell E (child 1 ) =XLOOKUP(A3,'2021-2022 Registration'!K4:K70,'2021-2022 Registration'!A4:A70)
Formula in Cell F (child 1 session) =XLOOKUP(E3,'2021-2022 Registration'!A4:A70,'2021-2022 Registration'!I4:I70)

Formula in Cell G (child 2) =XLOOKUP(A3,'2021-2022 Registration'!K4:K70,'2021-2022 Registration'!A4:A70,)
Formula in Cell H (child 2 session) =XLOOKUP(G3,'2021-2022 Registration'!A4:A70,'2021-2022 Registration'!I4:I70)

Basically, the same formula as cell E is in cells G, I, K and the same formula in Cell F is in cells H, J, L. In some cases, like in the parent listed in the first row, there are multiple children and so when XLookup tries to match the parent name and return child name, it returns the first match found which is already there. I would like to insert something in the formula bar to tell excel to exclude if it is already returned. Hope that makes sense.

A
E
F
G
H
I
J
K
L
Parent nameChild 1Child 1 SessionChild 2Child 2 SessionChild 3Child 3 SessionChild 4Child 3 Session
MeghanCalebWednesday 4:30 - Level 2CalebWednesday 4:30 - Level 2CalebWednesday 4:30 - Level 2CalebWednesday 4:30 - Level 2
BrittanyMaerie-IsabelTuesday 3:30 - Level 2Maerie-IsabelTuesday 3:30 - Level 2
RasheleAdalynnWednesday 4:30 - Level 1
BenjaminJustinWednesday 4:30 - Level 1
JamiEleanorMonday 1:30
KileHavenMonday 10:00
StevenSienaWednesday 10:00
JulieAnnabelleWednesday 1:30 - Level 1
MaryJolenaWednesday 1:30 - Level 1
KristaAnnelieseWednesday 10:00
KjerstinBridgetTuesday 3:30 - Level 3
CheriEliTuesday 3:30 - Level 2
MadelineCarolineMonday 1:30
LanceEmmeliaTuesday 3:30 - Level 2
LauraGeneviveWednesday 4:30 - Level 2
BriceAeneasWednesday 4:30 - Level 1
LisaAbigailWednesday 4:30 - Level 1
JoshuaCoraMonday 10:00
VictoriaDavidWednesday 10:00
EdwardJosephineWednesday 1:30 - Level 1
JessicaGeorgeWednesday 1:30 - Level 1
DennisJohnPaulWednesday 4:30 - Level 2
DeidreBridgidTuesday 3:30 - Level 3
KimberlyAnnalynneWednesday 1:30 - Level 1
EveAugustineWednesday 10:00
CarolynHarperTuesday 3:30 - Level 2
 

SrCM

New Member
Joined
Aug 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Here is the info this is in the registration tab that the formula is referring to

ChildParents
First NameAgeSacramentsGradeSession Day/timeLevelFirst NameFirst Name
Caleb7No3Wednesday 4:30 - Level 22MeghanBrendan
Dominic5NoKWednesday 4:30 - Level 11MeghanBrendan
Eli6Yes2Wednesday 4:30 - Level 22MeghanBrendan
Maerie-Isabel6Yes1Tuesday 3:30 - Level 22BrittanyChristopher
Adalynn5YesKWednesday 4:30 - Level 11RasheleWilliam
Avah6Yes1Wednesday 4:30 - Level 22RasheleWilliam
Isaac5NoPre-KWednesday 4:30 - Level 11RasheleWilliam
Justin4NoPre-KWednesday 4:30 - Level 11BenjaminChristina
Eleanor5NoKMonday 1:301JamiKevin
Rose9No4Tuesday 3:30 - Level 32JamiKevin
Justin7Yes2Tuesday 3:30 - Level 22JamiKevin
Haven3NoPre-KMonday 10:001KileChrist
Siena3NoWednesday 10:001StevenJocelyn
Annabelle3NoPre-KWednesday 1:30 - Level 11JulieJordan
Joshua8No3Wednesday 1:30 - Level 22JulieJordan
Madeline5Yes1Wednesday 1:30 - Level 11JulieJordan
Jolena4NoPre-KWednesday 1:30 - Level 11MaryJason
Liana6NoKWednesday 1:30 - Level 11MaryJason
Siena7Yes2Wednesday 1:30 - Level 22MaryJason
Anneliese3NoWednesday 10:001KristaDavid
Bridget11Yes6Tuesday 3:30 - Level 33KjerstinNick
Linus9Yes3Tuesday 3:30 - Level 33KjerstinNick
Pearl3NoPre-KMonday 1:301KjerstinNick
Phoebe6No1Tuesday 3:30 - Level 22KjerstinNick
Eli7Yes2Tuesday 3:30 - Level 22CheriZach
Finn3NoPre-KMonday 10:001CheriZach
Liam9No4Tuesday 3:30 - Level 32CheriZach
Caroline4NoPre-KMonday 1:301MadelineMichael
Emmelia7Yes2Tuesday 3:30 - Level 22LanceSuzanne
Genevive7No2Wednesday 4:30 - Level 22LauraMitchell
Serafina9No3Wednesday 4:30 - Level 22LauraMitchell
Aeneas5NoKWednesday 4:30 - Level 11BriceSteven
Abigail5Yes1Wednesday 4:30 - Level 11LisaEric
Jacob7Yes1Wednesday 4:30 - Level 22LisaEric
Cora5NoMonday 10:001JoshuaKate
David3NoPre-KWednesday 10:001VictoriaErik
Tommy5NoKWednesday 10:001VictoriaErik
Josephine4NoPre-KWednesday 1:30 - Level 11EdwardAdrienne
Magdalena5NoKWednesday 1:30 - Level 11EdwardAdrienne
Norah8No3Tuesday 3:30 - Level 22EdwardAdrienne
Owen7Yes2Tuesday 3:30 - Level 22EdwardAdrienne
George4NoPre-KWednesday 1:30 - Level 11JessicaMatthew
Henry6Yes1Wednesday 1:30 - Level 22JessicaMatthew
JohnPaul8No3Wednesday 4:30 - Level 22DennisAnna
Lucy4NoPre-KWednesday 4:30 - Level 11DennisAnna
Mariella2NoWednesday 4:30 - Level 11DennisAnna
Bridgid10No6Tuesday 3:30 - Level 33DeidreDavid
Daniel5NoPre-KMonday 1:301DeidreDavid
Victoria7Yes2Tuesday 3:30 - Level 22DeidreDavid
Annalynne5NoKWednesday 1:30 - Level 11KimberlyBryan
Ella9Yes4Wednesday 1:30 - Level 22KimberlyBryan
Kai3NoPre-KWednesday 1:30 - Level 11KimberlyBryan
Luke7Yes2Wednesday 1:30 - Level 22KimberlyBryan
Augustine3NoWednesday 10:001EveSean
Sophia5Yes1Tuesday 3:30 - Level 22EveSean
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,773
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for the sample data.

I have done this on one sheet but can be adapted to two. Also, this will not work on your Mac system, but should on your Windows version.

SrCM.xlsm
ABCDEFGHIJKLMNOPQR
1ChildParents
2First NameAgeSacramentsGradeSession Day/timeLevelFirst NameFirst NameMeghanCalebWednesday 4:30 - Level 2DominicWednesday 4:30 - Level 1EliWednesday 4:30 - Level 2
3Caleb7No3Wednesday 4:30 - Level 22MeghanBrendanBrittanyMaerie-IsabelTuesday 3:30 - Level 2
4Dominic5NoKWednesday 4:30 - Level 11MeghanBrendanRasheleAdalynnWednesday 4:30 - Level 1AvahWednesday 4:30 - Level 2IsaacWednesday 4:30 - Level 1
5Eli6Yes2Wednesday 4:30 - Level 22MeghanBrendanBenjaminJustinWednesday 4:30 - Level 1
6Maerie-Isabel6Yes1Tuesday 3:30 - Level 22BrittanyChristopherJamiEleanorMonday 1:30RoseTuesday 3:30 - Level 3JustinTuesday 3:30 - Level 2
7Adalynn5YesKWednesday 4:30 - Level 11RasheleWilliamKileHavenMonday 10:00
8Avah6Yes1Wednesday 4:30 - Level 22RasheleWilliamStevenSienaWednesday 10:00
9Isaac5NoPre-KWednesday 4:30 - Level 11RasheleWilliamJulieAnnabelleWednesday 1:30 - Level 1JoshuaWednesday 1:30 - Level 2MadelineWednesday 1:30 - Level 1
10Justin4NoPre-KWednesday 4:30 - Level 11BenjaminChristinaMaryJolenaWednesday 1:30 - Level 1LianaWednesday 1:30 - Level 1SienaWednesday 1:30 - Level 2
11Eleanor5NoKMonday 1:301JamiKevinKristaAnnelieseWednesday 10:00
12Rose9No4Tuesday 3:30 - Level 32JamiKevinKjerstinBridgetTuesday 3:30 - Level 3LinusTuesday 3:30 - Level 3PearlMonday 1:30PhoebeTuesday 3:30 - Level 2
13Justin7Yes2Tuesday 3:30 - Level 22JamiKevinCheriEliTuesday 3:30 - Level 2FinnMonday 10:00LiamTuesday 3:30 - Level 3
14Haven3NoPre-KMonday 10:001KileChristMadelineCarolineMonday 1:30
15Siena3NoWednesday 10:001StevenJocelynLanceEmmeliaTuesday 3:30 - Level 2
16Annabelle3NoPre-KWednesday 1:30 - Level 11JulieJordanLauraGeneviveWednesday 4:30 - Level 2SerafinaWednesday 4:30 - Level 2
17Joshua8No3Wednesday 1:30 - Level 22JulieJordanBriceAeneasWednesday 4:30 - Level 1
18Madeline5Yes1Wednesday 1:30 - Level 11JulieJordanLisaAbigailWednesday 4:30 - Level 1JacobWednesday 4:30 - Level 2
19Jolena4NoPre-KWednesday 1:30 - Level 11MaryJasonJoshuaCoraMonday 10:00
20Liana6NoKWednesday 1:30 - Level 11MaryJasonVictoriaDavidWednesday 10:00TommyWednesday 10:00
21Siena7Yes2Wednesday 1:30 - Level 22MaryJasonEdwardJosephineWednesday 1:30 - Level 1MagdalenaWednesday 1:30 - Level 1NorahTuesday 3:30 - Level 2OwenTuesday 3:30 - Level 2
22Anneliese3NoWednesday 10:001KristaDavidJessicaGeorgeWednesday 1:30 - Level 1HenryWednesday 1:30 - Level 2
23Bridget11Yes6Tuesday 3:30 - Level 33KjerstinNickDennisJohnPaulWednesday 4:30 - Level 2LucyWednesday 4:30 - Level 1MariellaWednesday 4:30 - Level 1
24Linus9Yes3Tuesday 3:30 - Level 33KjerstinNickDeidreBridgidTuesday 3:30 - Level 3DanielMonday 1:30VictoriaTuesday 3:30 - Level 2
25Pearl3NoPre-KMonday 1:301KjerstinNickKimberlyAnnalynneWednesday 1:30 - Level 1EllaWednesday 1:30 - Level 2KaiWednesday 1:30 - Level 1LukeWednesday 1:30 - Level 2
26Phoebe6No1Tuesday 3:30 - Level 22KjerstinNickEveAugustineWednesday 10:00SophiaTuesday 3:30 - Level 2
27Eli7Yes2Tuesday 3:30 - Level 22CheriZach
28Finn3NoPre-KMonday 10:001CheriZach
29Liam9No4Tuesday 3:30 - Level 32CheriZach
30Caroline4NoPre-KMonday 1:301MadelineMichael
31Emmelia7Yes2Tuesday 3:30 - Level 22LanceSuzanne
32Genevive7No2Wednesday 4:30 - Level 22LauraMitchell
33Serafina9No3Wednesday 4:30 - Level 22LauraMitchell
Sheet1
Cell Formulas
RangeFormula
J2:J26J2=UNIQUE(G3:G57)
K2:P2,K26:N26,K25:R25,K23:P24,K22:N22,K21:R21,K20:N20,K19:L19,K18:N18,K17:L17,K16:N16,K14:L15,K13:P13,K12:R12,K11:L11,K9:P10,K7:L8,K6:P6,K5:L5,K4:P4,K3:L3K2=TRANSPOSE(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",0,FILTER(A$3:A$57&"</c><c>"&E$3:E$57,G$3:G$57=J2))&"</c></p>","//c"))
Dynamic array formulas.
 
Solution

SrCM

New Member
Joined
Aug 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Thanks for the sample data.

I have done this on one sheet but can be adapted to two. Also, this will not work on your Mac system, but should on your Windows version.

SrCM.xlsm
ABCDEFGHIJKLMNOPQR
1ChildParents
2First NameAgeSacramentsGradeSession Day/timeLevelFirst NameFirst NameMeghanCalebWednesday 4:30 - Level 2DominicWednesday 4:30 - Level 1EliWednesday 4:30 - Level 2
3Caleb7No3Wednesday 4:30 - Level 22MeghanBrendanBrittanyMaerie-IsabelTuesday 3:30 - Level 2
4Dominic5NoKWednesday 4:30 - Level 11MeghanBrendanRasheleAdalynnWednesday 4:30 - Level 1AvahWednesday 4:30 - Level 2IsaacWednesday 4:30 - Level 1
5Eli6Yes2Wednesday 4:30 - Level 22MeghanBrendanBenjaminJustinWednesday 4:30 - Level 1
6Maerie-Isabel6Yes1Tuesday 3:30 - Level 22BrittanyChristopherJamiEleanorMonday 1:30RoseTuesday 3:30 - Level 3JustinTuesday 3:30 - Level 2
7Adalynn5YesKWednesday 4:30 - Level 11RasheleWilliamKileHavenMonday 10:00
8Avah6Yes1Wednesday 4:30 - Level 22RasheleWilliamStevenSienaWednesday 10:00
9Isaac5NoPre-KWednesday 4:30 - Level 11RasheleWilliamJulieAnnabelleWednesday 1:30 - Level 1JoshuaWednesday 1:30 - Level 2MadelineWednesday 1:30 - Level 1
10Justin4NoPre-KWednesday 4:30 - Level 11BenjaminChristinaMaryJolenaWednesday 1:30 - Level 1LianaWednesday 1:30 - Level 1SienaWednesday 1:30 - Level 2
11Eleanor5NoKMonday 1:301JamiKevinKristaAnnelieseWednesday 10:00
12Rose9No4Tuesday 3:30 - Level 32JamiKevinKjerstinBridgetTuesday 3:30 - Level 3LinusTuesday 3:30 - Level 3PearlMonday 1:30PhoebeTuesday 3:30 - Level 2
13Justin7Yes2Tuesday 3:30 - Level 22JamiKevinCheriEliTuesday 3:30 - Level 2FinnMonday 10:00LiamTuesday 3:30 - Level 3
14Haven3NoPre-KMonday 10:001KileChristMadelineCarolineMonday 1:30
15Siena3NoWednesday 10:001StevenJocelynLanceEmmeliaTuesday 3:30 - Level 2
16Annabelle3NoPre-KWednesday 1:30 - Level 11JulieJordanLauraGeneviveWednesday 4:30 - Level 2SerafinaWednesday 4:30 - Level 2
17Joshua8No3Wednesday 1:30 - Level 22JulieJordanBriceAeneasWednesday 4:30 - Level 1
18Madeline5Yes1Wednesday 1:30 - Level 11JulieJordanLisaAbigailWednesday 4:30 - Level 1JacobWednesday 4:30 - Level 2
19Jolena4NoPre-KWednesday 1:30 - Level 11MaryJasonJoshuaCoraMonday 10:00
20Liana6NoKWednesday 1:30 - Level 11MaryJasonVictoriaDavidWednesday 10:00TommyWednesday 10:00
21Siena7Yes2Wednesday 1:30 - Level 22MaryJasonEdwardJosephineWednesday 1:30 - Level 1MagdalenaWednesday 1:30 - Level 1NorahTuesday 3:30 - Level 2OwenTuesday 3:30 - Level 2
22Anneliese3NoWednesday 10:001KristaDavidJessicaGeorgeWednesday 1:30 - Level 1HenryWednesday 1:30 - Level 2
23Bridget11Yes6Tuesday 3:30 - Level 33KjerstinNickDennisJohnPaulWednesday 4:30 - Level 2LucyWednesday 4:30 - Level 1MariellaWednesday 4:30 - Level 1
24Linus9Yes3Tuesday 3:30 - Level 33KjerstinNickDeidreBridgidTuesday 3:30 - Level 3DanielMonday 1:30VictoriaTuesday 3:30 - Level 2
25Pearl3NoPre-KMonday 1:301KjerstinNickKimberlyAnnalynneWednesday 1:30 - Level 1EllaWednesday 1:30 - Level 2KaiWednesday 1:30 - Level 1LukeWednesday 1:30 - Level 2
26Phoebe6No1Tuesday 3:30 - Level 22KjerstinNickEveAugustineWednesday 10:00SophiaTuesday 3:30 - Level 2
27Eli7Yes2Tuesday 3:30 - Level 22CheriZach
28Finn3NoPre-KMonday 10:001CheriZach
29Liam9No4Tuesday 3:30 - Level 32CheriZach
30Caroline4NoPre-KMonday 1:301MadelineMichael
31Emmelia7Yes2Tuesday 3:30 - Level 22LanceSuzanne
32Genevive7No2Wednesday 4:30 - Level 22LauraMitchell
33Serafina9No3Wednesday 4:30 - Level 22LauraMitchell
Sheet1
Cell Formulas
RangeFormula
J2:J26J2=UNIQUE(G3:G57)
K2:P2,K26:N26,K25:R25,K23:P24,K22:N22,K21:R21,K20:N20,K19:L19,K18:N18,K17:L17,K16:N16,K14:L15,K13:P13,K12:R12,K11:L11,K9:P10,K7:L8,K6:P6,K5:L5,K4:P4,K3:L3K2=TRANSPOSE(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",0,FILTER(A$3:A$57&"</c><c>"&E$3:E$57,G$3:G$57=J2))&"</c></p>","//c"))
Dynamic array formulas.
oh my goodness YOU.ARE.A.LIFESAVER!!!!

This has been a huge boulder in delaying my email to parents confirming details. I have many more to do so I plan on using thie book as a template. I will try and figure out what xcel slight of hand this was later.

Thank you again sooo sooo much!!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,773
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad it worked for you. Thanks for the follow-up. :)
 

Forum statistics

Threads
1,147,958
Messages
5,744,043
Members
423,843
Latest member
alex2022

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