Index Match with different outcomes

[jwoww]

I am doing an alternative schedule for something and depending on what the drop down in column a says, sheet 2 has the option and corresponding text for that option i want to be reflected, but i also want it to add what is written in column c to the end. The thing that weirds out my formula is that there are certain drop down options that need to add what is in column b instead of column c.

EG - memorial service in column a will deliver the following text "the family attended a memorial service at the local church" but if column a said festival, the text would read "the family attended the Dog Festival"

example1.xlsx

 

[etaf]

if you add the cell you wan to column C on sheet 2
and then use
=INDEX(Sheet2!B1:B28,MATCH(Sheet1!A1,Sheet2!A1:A28,0))&" "&INDIRECT(INDEX(Sheet2!C1:C28,MATCH(Sheet1!A1,Sheet2!A1:A28,0)))

see a4

example1.xlsx
	A	B	C
1	FESTIVAL	Dog Festival	Local Church
2	#N/A
3
4	The family attend the Dog Festival
Sheet1

Cell Formulas
Range	Formula
A2	A2	=INDEX(Sheet2!$B$1:$B$22,((MATCH(A1,Sheet2!$A$1:$A$22,0))&C1),(MATCH(A1,Sheet2!$A$23:$A$24,0))&B1)
A4	A4	=INDEX(Sheet2!B1:B28,MATCH(Sheet1!A1,Sheet2!A1:A28,0))&" "&INDIRECT(INDEX(Sheet2!C1:C28,MATCH(Sheet1!A1,Sheet2!A1:A28,0)))

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=Sheet2!$A$1:$A$28

not sure what you want c1 or b1 for all the other entries

example1.xlsx
	A	B	C
1	ARRIVE	The family arrive at
2	BREAKFAST	The family attend a breakfast at
3	BRIEFING	The family attend a Briefing at
4	CEREMONY	The family attend a Ceremony at
5	CHURCH SERVICE	The family attend a Church Service at
6	COMMEMORATIVE SERVICE	The family attend a Commemorative Service at
7	DINNER	The family attend a Dinner at
8	FUNERAL SERVICE	The family attend a Funeral Service at
9	LUNCH	The family attend a Lunch at
10	MEETING	The family attend a Meeting at
11	MEMORIAL SERVICE	The family attend a Memorial Service at the	c1
12	MORNING TEA	The family attend a Morning Tea at
13	PERFORMANCE	The family attend a Performance at
14	RECEPTION	The family attend a Reception at
15	SCHOOL VISIT	The family attend a School Visit of
16	AWARD GALA	The family attend an Award Gala at
17	AWARDS CEREMONY	The family attend an Awards Ceremony at
18	EVENT	The family attend an Event at
19	TRAVEL	The family Travel to
20	VISIT	The family Visit
21	GARDEN PARTY	The family host a Garden Party at
22	AFTERNOON TEA	The family host an Afternoon Tea at
23	BALL	The family attend the
24	FESTIVAL	The family attend the	b1
25	CAR	The family depart
26	DEPART	The family depart
27	FLIGHT	The family depart
28	VIDEO RECORDING	The family Record a Video for
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:B28	Other Type	Color scale		NO

link to file here - BUT i only leave on dropbox for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[jwoww]

Thanks for what you've done, but when i copy it back into my main spreadsheet (where i need it to go) the phrase that gets copied from sheet 2 doesn't add either the location in column c or the option in column b (depending on what column a says)

 

[etaf]

can you post the formula - you have entered
and where the Location and option cell is - column C & B - but need the full cell reference C1 , B1 and if you have added that onto the table


or perhaps - if you have no secure data - perhaps save to a share
if you cannot use XL2BB

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[jwoww]

I've uploaded a copy of the actual spreadsheet i will be using (Sheet 1 will be repeated for each of the days throughout the year). The uploaded version just shows random options but column H shows what would be expected to be shown in column d based on what is written in the row above each "timeframe"

The formula box has a couple of option specific formulas based on the response required for that option (ie adding multiple cell words to various positions in a sentence)

Daily Schedule 2023 v4.xlsx

 

[etaf]

in column C on sheet 2 - you have the cell you need to reference
C1 and B1 seems to be the only entries

if i then look at sheet1
C1
B1
you have a merged cell from A1 to F1
so nothing in C1 or B1

so i'm not sure what you are trying to do - in the this latest sheet ????

 

[jwoww]

in column C on sheet 2 - you have the cell you need to reference
C1 and B1 seems to be the only entries

if i then look at sheet1
C1
B1
you have a merged cell from A1 to F1
so nothing in C1 or B1

so i'm not sure what you are trying to do - in the this latest sheet ????

this sheet is the actual version of what i will be using.

In its simplest form, sheet 1 will list each day's schedule one after another. my formula i need help with needs to determine what the event type will be (cell D5, D11, D14, D17 etc) is and determine what sentence (from sheet 2 column b) corresponds with and inputs that into the relevant cell (D6, D12, D15, D18 etc) and add what appears in column F (cell F5, F11, F14, F17 etc) or column E (cell E5, E11, E14, E17 etc). What determines if either column F or column E is used is whether the event type on sheet 2 is between row 2-row 24 or if its row 25-row 27.

 

[etaf]

I have changed the C1 and B1 in the lookup table to just 2 or 1
and then used
=INDEX(Sheet2!$B$3:$B$27,MATCH(Sheet1!D5,Sheet2!$A$3:$A$27,0))&IF(INDEX(Sheet2!$C$3:$C$27,MATCH(Sheet1!D5,Sheet2!$A$3:$A$27,0))=2,F5,E5)

that now works with the correct cells copied down

so if the lookup is not a 2 - it will chose E5 .... can nest further if you need other options

=IF(D5="Daily Senior Staff Briefing",("XXXX receives his Daily Senior Staff Briefing"),IF(D5="FLIGHT",("XXXX depart "&F5&" via Plane enroute to "&F8),IF(D5="DEPART",("XXXX depart "&F5&" via Car enroute to "&F8),IF(D5="CAR",("XXXX depart "&F5&" via Car enroute to "&F8),IF(D5="Relax Time",("xxxxx have no events scheduled during this time"),IF(D5="Down Time",("xxxxx have no public events scheduled"),INDEX(Sheet2!$B$3:$B$27,MATCH(Sheet1!D5,Sheet2!$A$3:$A$27,0))&IF(INDEX(Sheet2!$C$3:$C$27,MATCH(Sheet1!D5,Sheet2!$A$3:$A$27,0))=2,F5,E5)))))))

no need for any indirect
just a lookup and IF

may be able to simplify further - but thats

on dropbox - BUT only for a few days

Daily Schedule 2023 v4 -ETAF.xlsx

Shared with Dropbox

www.dropbox.com

Daily Schedule 2023 v4 -ETAF.xlsx
	A	B	C	D	E	F	G	H
1	SCHEDULE FOR XXXXXXXX
2	Sunday, 1 January 2023
3	LOCATIONS
4
5	8:30 AM	8:55 AM	0:25	MEMORIAL SERVICE		Home
6	25 min			xxxxx attend a Memorial Service at Home				xxxxx attend a Memorial Service at Home
7
8	9:00 AM	9:45 AM	0:45	PERFORMANCE	Dog Fesitval	Sports Park
9	45 min			xxxxx attend a Performance at Sports Park				xxxxx attend a Performance at Sports Park
10
11	9:50 AM	11:00 AM	1:10	CAR	Literacy Festival	Library
12	1 hr, 10 min			XXXX depart Library via Car enroute to Shopping Centre				XXXX depart Library via Car enroute to Shopping Centre
13
14	11:05 AM	11:05 AM		AWARD GALA		Shopping Centre
15				xxxxx attend an Award Gala at Shopping Centre				xxxxx attend an Award Gala at Shopping Centre
16
17	11:10 AM	11:10 AM		AWARDS CEREMONY	Best Swimmer	Beach
18				xxxxx attend an Awards Ceremony at Beach				xxxxx attend an Awards Ceremony at Beach
19
20	11:15 AM	11:15 AM		GARDEN PARTY		Theme Park
21				xxxxx host a Garden Party at Theme Park				xxxxx host a Garden Party at Theme Park
22
23	11:20 AM	11:20 AM		AFTERNOON TEA		Swimming Pool
24				xxxxx host an Afternoon Tea atSwimming Pool				xxxxx host an Afternoon Tea at Swimming Pool
25
26	11:25 AM	11:25 AM		TRAVEL		Airport
27				xxxxx Travel to Airport				xxxxx Travel to Airport
28
29	11:30 AM	11:30 AM		SCHOOL VISIT		College
30				xxxxx attend a School Visit of College				xxxxx attend a School Visit of College
31
32	11:35 AM	11:35 AM		BALL	Football Ball	Sports Centre
33				xxxxx attend the Football Ball				xxxxx attend the Football Ball
34
35	11:40 AM	11:40 AM		FLIGHT		Airport
36				XXXX depart Airport via Plane enroute to Hockey Rink				XXXX depart Airport via Plane enroute to Hockey Rink
37
38	11:55 AM	11:55 AM		RELAX TIME		Hockey Rink
39				xxxxx have no events scheduled during this time				xxxxx have no events scheduled during this time
40
41	12:00 PM	12:00 PM		FESTIVAL	Chicken Festival	Restaurant
42				xxxxx attend the Chicken Festival				xxxxx attend the Chicken Festival
43
44	12:05 PM	12:05 PM		CEREMONY		Carpark
45				xxxxx attend a Ceremony at Carpark				xxxxx attend a Ceremony at Carpark
46
47	12:10 PM	12:10 PM		LUNCH		Hotel
48				xxxxx attend a Lunch at Hotel				xxxxx attend a Lunch at Hotel
49
50	RON XXX			Home
51	RON XXX			Hotel
52	RON XXX			Hotel
53
54
55
56
57
58
Sheet1

Cell Formulas
Range	Formula
A5	A5	=A2+"8:30"
B5,B47,B44,B41,B38,B35,B32,B29,B26,B23,B20,B17,B14,B11,B8	B5	=IF(D5="DOWN TIME","",(CEILING((A5+C5),"0:05")))
A6,A48,A45,A42,A39,A36,A33,A30,A27,A24,A21,A18,A15,A12,A9	A6	=IF(C5="","",IF(HOUR(C5)>0,TEXT(C5,"h")&" hr, ","")&RIGHT(TEXT(C5,"hh:mm"),2)&" min")
D6,D48,D45,D42,D39,D36,D33,D30,D27,D24,D21,D18,D15,D12,D9	D6	=IF(D5="Daily Senior Staff Briefing",("XXXX receives his Daily Senior Staff Briefing"),IF(D5="FLIGHT",("XXXX depart "&F5&" via Plane enroute to "&F8),IF(D5="DEPART",("XXXX depart "&F5&" via Car enroute to "&F8),IF(D5="CAR",("XXXX depart "&F5&" via Car enroute to "&F8),IF(D5="Relax Time",("xxxxx have no events scheduled during this time"),IF(D5="Down Time",("xxxxx have no public events scheduled"),INDEX(Sheet2!$B$3:$B$27,MATCH(Sheet1!D5,Sheet2!$A$3:$A$27,0))&IF(INDEX(Sheet2!$C$3:$C$27,MATCH(Sheet1!D5,Sheet2!$A$3:$A$27,0))=2,F5,E5)))))))
A8,A47,A44,A41,A38,A35,A32,A29,A26,A23,A20,A17,A14,A11	A8	=CEILING((B5+"0:05"),"00:05")+(IF(D5="Flight","0:10"))

Cells with Data Validation
Cell	Allow	Criteria
D50:D52	List	=Sheet2!$F$2:$F$27
D5	List	=Sheet2!$A$1:$A$48
D8	List	=Sheet2!$A$1:$A$48
D11	List	=Sheet2!$A$1:$A$48
D14	List	=Sheet2!$A$1:$A$48
D17	List	=Sheet2!$A$1:$A$48
D20	List	=Sheet2!$A$1:$A$48
D23	List	=Sheet2!$A$1:$A$48
D26	List	=Sheet2!$A$1:$A$48
D29	List	=Sheet2!$A$1:$A$48
D32	List	=Sheet2!$A$1:$A$48
D35	List	=Sheet2!$A$1:$A$48
D38	List	=Sheet2!$A$1:$A$48
D41	List	=Sheet2!$A$1:$A$48
D44	List	=Sheet2!$A$1:$A$48
D47	List	=Sheet2!$A$1:$A$48

Daily Schedule 2023 v4 -ETAF.xlsx
	A	B	C	D	E	F
1	EVENT TYPE			CATEGORY	DURATION	RON
2	DAILY SENIOR STAFF BRIEFING	xxxxx receives his Daily Senior Staff Briefing			FLIGHT TIME	Home
3	AFTERNOON TEA	xxxxx host an Afternoon Tea at	2		DRIVE TIME	Hotel
4	ARRIVE	xxxxx arrive at	2
5	AWARD GALA	xxxxx attend an Award Gala at	2
6	AWARDS CEREMONY	xxxxx attend an Awards Ceremony at	2
7	BREAKFAST	xxxxx attend a breakfast at	2
8	BRIEFING	xxxxx attend a Briefing at	2
9	CEREMONY	xxxxx attend a Ceremony at	2
10	CHURCH SERVICE	xxxxx attend a Church Service at	2
11	COMMEMORATIVE SERVICE	xxxxx attend a Commemorative Service at	2
12	DINNER	xxxxx attend a Dinner at	2
13	EVENT	xxxxx attend an Event at	2
14	FUNERAL SERVICE	xxxxx attend a Funeral Service at	2
15	GARDEN PARTY	xxxxx host a Garden Party at	2
16	LUNCH	xxxxx attend a Lunch at	2
17	MEETING	xxxxx attend a Meeting at	2
18	MEMORIAL SERVICE	xxxxx attend a Memorial Service at	2
19	MORNING TEA	xxxxx attend a Morning Tea at	2
20	PERFORMANCE	xxxxx attend a Performance at	2
21	RECEPTION	xxxxx attend a Reception at	2
22	SCHOOL VISIT	xxxxx attend a School Visit of	2
23	TRAVEL	xxxxx Travel to	2
24	VISIT	xxxxx Visit	2
25	BALL	xxxxx attend the	1
26	FESTIVAL	xxxxx attend the	1
27	VIDEO RECORDING	xxxxx Record a Video for	1
28	CAR	xxxxx depart
29	DEPART	xxxxx depart
30	FLIGHT	xxxxx depart
31	RELAX TIME	xxxxx have no events scheduled during this time
32	DOWN TIME	xxxxx have no public events scheduled
33
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C32	Other Type	Color scale		NO