Lookup/Match Based on Multiple Details (non-VBA)

[default_name]

Hey guys,

I am trying to set up a spreadsheet to fill in time slots based on day and time information.
There are two sheets in the workbook (Calendar and Log)

Here is how I have it set up:
The name of this sheet is Calendar

	A	B	C
1		Wednesday (August 19, 2020)	Thursday (August 20, 2020)
2	900 AM
3	940 AM
4	1020 AM
5	1100 AM
6	1140 AM

The name of this sheet is Log

	A	B	C	D	E
1		Family Name	Phone Number	Scheduled Day	Scheduled Time
2		Smith	619-555-7600	Wednesday (August 19, 2020)	1020 AM - 1100 AM
3		Jones	619-555-2432	Wednesday (August 19, 2020)	900 AM - 940 AM
4		Johnson	600-354-8434	Thursday (August 20, 2020)	900 AM - 940 AM
5		Wilkins	685-879-4546	Wednesday (August 19, 2020)	940 AM - 1020 AM
6		Cortez	619-856-9952	Thursday (August 20, 2020)	1140 AM - 1220 PM

I would like the Calendar sheet to fill in the correct cells of the scheduled appointments, based on the data found in the Log sheet.
Basically, given the data that is currently in the Log sheet, the Calendar sheet should end up looking like this:

	A	B	C
1		Wednesday (August 19, 2020)	Thursday (August 20, 2020)
2	900 AM	Jones 619-555-2432	Johnson 600-354-8434
3	940 AM	Wilkins 685-879-4546
4	1020 AM	Smith 619-555-7600
5	1100 AM
6	1140 AM		Cortez 619-856-9952

Is there some sort of IF, LOOKUP or MATCH function that I can use in the cells of the Calendar sheet?
I tried using various mixtures of LOOKUP statements, AND statements, and IF statements, but it quickly became really complicated.
The workbook cannot be macro-enabled, so I am looking for a NON-VBA solution.
I hope this makes sense. Let me know if I need to clarify/simplify anything.

You guys are the best! Thanks in advance!

 

[Peter_SSs]

I'm not sure whether I will have an answer but in any case a few questions are needed to clarify what we are dealing with:

1. Can you please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
2. Are the values in B1, C1, .. of Calendar and column D of Log actual dates (Numbers) formatted to look like they do or are they Text values
3. Are the values in column A of Calendar actual times (Numbers) formatted to look like they do or are they Text values
4. Will there ever be more than one thing on at the same time on the same day?
5. Will a scheduled time in column E of Log ever be more (or less) than 40 minutes?

I also suggest that you investigate XL2BB for providing sample data.

 

[default_name]

I'm not sure whether I will have an answer but in any case a few questions are needed to clarify what we are dealing with:

1. Can you please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
2. Are the values in B1, C1, .. of Calendar and column D of Log actual dates (Numbers) formatted to look like they do or are they Text values
3. Are the values in column A of Calendar actual times (Numbers) formatted to look like they do or are they Text values
4. Will there ever be more than one thing on at the same time on the same day?
5. Will a scheduled time in column E of Log ever be more (or less) than 40 minutes?

I also suggest that you investigate XL2BB for providing sample data.

1. I use Excel 2016 (Mac) but the spreadsheet will also be used by 2016 (Windows)
2. The values are all text values
3. The values are all text values
4. There will never be more than one person per each day/time
5. Scheduled blocks will always be 40 minutes

Thanks

 

[Peter_SSs]

Thanks for updating your profile and the clear answers to my questions.

Try this copied across and down (after adjusting the ranges to cover the data in the Log sheet if 1000 rows is not suitable) and format these columns to 'Wrap Text'.

default_name 2020-08-09 1.xlsm
	A	B	C
1		Wednesday (August 19, 2020)	Thursday (August 20, 2020)
2	900 AM	Jones 619-555-2432	Johnson 600-354-8434
3	940 AM	Wilkins 685-879-4546
4	1020 AM	Smith 619-555-7600
5	1100 AM
6	1140 AM		Cortez 619-856-9952
Calendar

Cell Formulas
Range	Formula
B2:C6	B2	=IFERROR(INDEX(Log!$A:$A,AGGREGATE(15,6,ROW(Log!$C$2:$C$1000)/((Log!$C$2:$C$1000=B$1)*(SEARCH($A2,Log!$D$2:$D$1000)=1)),1))&CHAR(10)&INDEX(Log!$B:$B,AGGREGATE(15,6,ROW(Log!$C$2:$C$1000)/((Log!$C$2:$C$1000=B$1)*(SEARCH($A2,Log!$D$2:$D$1000)=1)),1)),"")

 

[default_name]

You are so awesome! Thank you!!

 

[Peter_SSs]

You're very welcome. Thanks for the follow-up.