XLOOKUP, VLOOKUP, HLOOKUP and INDEX, MATCH

[ozzborn]

I use Excel 365 and Windows 11.

I am trying to create a meeting dashboard to track important meetings for my supervisor. I have a main data set sheet1 that looks like the first picture

I want to develop a formula that I can organize them into a spreadsheet that shows meetings occurring Today, This Week, and Next Week.

I have tried various formulas but it is when I try and integrate the date function that gets me confused.
One other condition is there can be up to 4 Topics scheduled on the same date. My formula would stop working after it found the 1st date.

The meetings are scheduled randomly and will not be in order ..... however the meetings are always Tue, Wed, and Fri.
It represent a review process that depending the the topic where it enters into the process.

This example below show one example of the Today table where it show all meeting schedule for "Today"
Meeting #2 and Meeting #3 will be blank in this example but if the Today's date was Oct 19th then it would show
all scheduled Topics for Meeting #2.


This example shows the This Week Table where is shows all meetings scheduled for the current work week.

Finally the next table shows the "next week" shows all meetings scheduled the next week.
These of course will change based on the Today's date ....and as the meetings are scheduled.
The meetings are often changed and deleted depending on the what is validated and at what meeting.

Thanks in advance for any help someone might be able to lend.
Ozz

 

[keef2]

Maybe something like this?

Book1
	A	B	C	D
1	Meeting 1	Meeting 2	Meeting 3
2	Tue	Wed	Fri
3	10/18/2022	11/16/2022	12/9/2022	Topic 1
4	10/18/2022	9/21/2022		Topic 2
5	10/25/2022	18-Oct		Topic 3
6	10/25/2022			Topic 4
7	11/8/2022	12/7/2022	1/6/2023	Topic 5
8		11/30/2022	12/16/2022	Topic 6
9	10/11/2022			Topic 7
10		10/19/2022	10/21/2022	Topic 8
11		10/26/2022	10/28/2022	Topic 9
12	10/18/2022		10/28/2022	Topic 10
13	10/18/2022	10/26/2022	10/28/2022	Topic 11
14	11/15/2022			Topic 12
15
16
17
18	Todays Date	10/18/2022
19	Meeting 1	Topic
20	10/18/2022	Topic 1
21	10/18/2022	Topic 2
22	10/18/2022	Topic 10
23	10/18/2022	Topic 11
24
25	Meeting 2
26	10/18/2022	Topic 3
27
28
29
30	Meeting 3
31
32
Sheet1

Cell Formulas
Range	Formula
A20:A23	A20	=IFERROR(FILTER($A$3:$A$14,$A$3:$A$14=$B$18),"")
B20:B23	B20	=IFERROR(FILTER($D$3:$D$14,$A$3:$A$14=$B$18),"")
A24,B24:B25,B27:B30,A27:A29	A24	=IFERROR(INDEX(A$3:A$14,AGGREGATE(15,6,(ROW($A$3:$A$14)-ROW($A$3)+1)/(ISNUMBER(MATCH($A$3:$A$14,$B$18,0))),ROWS(B$18:B22))),"")
A26	A26	=IFERROR(FILTER($B$3:$B$14,$B$3:$B$14=$B$18),"")
B26	B26	=IFERROR(FILTER($D$3:$D$14,$B$3:$B$14=$B$18),"")
A31	A31	=IFERROR(FILTER($C$3:$C$14,$C$3:$C$14=$B$18),"")
B31	B31	=IFERROR(FILTER($D$3:$D$14,$C$3:$C$14=$B$18),"")
Dynamic array formulas.

 

[ozzborn]

Let me look at this. That was fast.

The problem I am having is the primary Data set in on Sheet1 and the other tables are on Sheet2.

Trying to apply the difference.

 

[keef2]

Ignore A24 must have been a carried over formula.

 

[ozzborn]

Ok, I was wondering about A24.

A20 I am getting a #SPILL! message
A21 I am getting a #SPILL! message
A22 I am getting a #SPILL! message
A23 I am getting 18 OCT not sure why.

Next is range B20-B23

 

[ozzborn]

Ok this is formula based on my Spreadsheet cells.

A20 =IFERROR(FILTER(Sheet1!$B$3:$B$14,Sheet1!$B$3:$B$14=$C$2),"") and it does not seem to be working result is " #SPILL! "
B20 =IFERROR(FILTER(Sheet1!$E$3:$E$14,Sheet1!$B$3:$B$14=$C$2),"") and it does not seem to be working result is restated formula " =IFERROR(FILTER(Sheet1!$E$3:$E$14,Sheet1!$B$3:$B$14=$C$2),"")


Somehow one of my examples did not get included with the original post. Not sure it makes much deafferents.

 

[keef2]

Can you post with a snip with XLB22 by chance? This way its easier to diagnose the issue. SPILL usually happens because you don't have enough space for the data to auto populate with the function. Do you have any data or other formulas?

 

[Fluff]

Clear all cells from A20 & below & then re-enter the formula in A20 only (do NOT use Ctl Shift Enter)
For B20 change the cell to General & then re-enter the formula.

 

[ozzborn]

Fluff / Keef2

Fluff, that worked cleaned up the formulas and bingo it populated.

Keef2. thank you for responding so quick and the initial formulas, pretty slick.

Ozz

 

[ozzborn]

Keef2, can you recommend the best method to learn quickly how to post with XLB22?