# Index Match with different outcomes



## jwoww (Dec 15, 2022)

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 (Dec 15, 2022)

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.xlsxABC1FESTIVALDog FestivalLocal Church2#N/A34The family attend the  Dog FestivalSheet1Cell FormulasRangeFormulaA2A2=INDEX(Sheet2!$B$1:$B$22,((MATCH(A1,Sheet2!$A$1:$A$22,0))&C1),(MATCH(A1,Sheet2!$A$23:$A$24,0))&B1)A4A4=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 ValidationCellAllowCriteriaA1List=Sheet2!$A$1:$A$28

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

example1.xlsxABC1ARRIVEThe family arrive at 2BREAKFASTThe family attend a breakfast at 3BRIEFINGThe family attend a Briefing at 4CEREMONYThe family attend a Ceremony at 5CHURCH SERVICEThe family attend a Church Service at 6COMMEMORATIVE SERVICEThe family attend a Commemorative Service at 7DINNERThe family attend a Dinner at 8FUNERAL SERVICEThe family attend a Funeral Service at 9LUNCHThe family attend a Lunch at 10MEETINGThe family attend a Meeting at 11MEMORIAL SERVICEThe family attend a Memorial Service at thec112MORNING TEAThe family attend a Morning Tea at 13PERFORMANCEThe family attend a Performance at 14RECEPTIONThe family attend a Reception at 15SCHOOL VISITThe family attend a School Visit of 16AWARD GALAThe family attend an Award Gala at 17AWARDS CEREMONYThe family attend an Awards Ceremony at 18EVENTThe family attend an Event at 19TRAVELThe family Travel to 20VISITThe family Visit 21GARDEN PARTYThe family host a Garden Party at 22AFTERNOON TEAThe family host an Afternoon Tea at23BALLThe family attend the 24FESTIVALThe family attend the b125CARThe family depart 26DEPARTThe family depart 27FLIGHTThe family depart 28VIDEO RECORDINGThe family Record a Video for Sheet2Cells with Conditional FormattingCellConditionCell FormatStop If TrueA1:B28Other TypeColor scaleNO

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








						concat indirect-ETAF.xlsx
					

Shared with Dropbox




					www.dropbox.com


----------



## jwoww (Dec 16, 2022)

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 (Dec 17, 2022)

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 (Dec 17, 2022)

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 (Dec 18, 2022)

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 (Dec 19, 2022)

etaf said:


> 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
> ...


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 (Dec 19, 2022)

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.xlsxABCDEFGH1SCHEDULE FOR XXXXXXXX2Sunday, 1 January 20233LOCATIONS 458:30 AM8:55 AM0:25MEMORIAL SERVICEHome625 minxxxxx attend a Memorial Service at Homexxxxx attend a Memorial Service at Home789:00 AM9:45 AM0:45PERFORMANCEDog FesitvalSports Park945 minxxxxx attend a Performance at Sports Parkxxxxx attend a Performance at Sports Park10119:50 AM11:00 AM1:10CARLiteracy FestivalLibrary121 hr, 10 minXXXX depart Library via Car enroute to Shopping CentreXXXX depart Library via Car enroute to Shopping Centre131411:05 AM11:05 AMAWARD GALAShopping Centre15 xxxxx attend an Award Gala at Shopping Centrexxxxx attend an Award Gala at Shopping Centre161711:10 AM11:10 AMAWARDS CEREMONYBest SwimmerBeach18 xxxxx attend an Awards Ceremony at Beachxxxxx attend an Awards Ceremony at Beach192011:15 AM11:15 AMGARDEN PARTYTheme Park21 xxxxx host a Garden Party at Theme Parkxxxxx host a Garden Party at Theme Park222311:20 AM11:20 AMAFTERNOON TEASwimming Pool24 xxxxx host an Afternoon Tea atSwimming Poolxxxxx host an Afternoon Tea at Swimming Pool252611:25 AM11:25 AMTRAVELAirport27 xxxxx Travel to Airportxxxxx Travel to Airport282911:30 AM11:30 AMSCHOOL VISITCollege30 xxxxx attend a School Visit of Collegexxxxx attend a School Visit of College313211:35 AM11:35 AMBALLFootball BallSports Centre33 xxxxx attend the Football Ballxxxxx attend the Football Ball343511:40 AM11:40 AMFLIGHTAirport36 XXXX depart Airport via Plane enroute to Hockey RinkXXXX depart Airport via Plane enroute to Hockey Rink373811:55 AM11:55 AMRELAX TIMEHockey Rink39 xxxxx have no events scheduled during this timexxxxx have no events scheduled during this time404112:00 PM12:00 PMFESTIVALChicken FestivalRestaurant42 xxxxx attend the Chicken Festivalxxxxx attend the Chicken Festival434412:05 PM12:05 PMCEREMONYCarpark45 xxxxx attend a Ceremony at Carparkxxxxx attend a Ceremony at Carpark464712:10 PM12:10 PMLUNCHHotel48 xxxxx attend a Lunch at Hotelxxxxx attend a Lunch at Hotel4950RON XXX Home51RON XXX Hotel52RON XXX Hotel535455565758Sheet1Cell FormulasRangeFormulaA5A5=A2+"8:30"B5,B47,B44,B41,B38,B35,B32,B29,B26,B23,B20,B17,B14,B11,B8B5=IF(D5="DOWN TIME","",(CEILING((A5+C5),"0:05")))A6,A48,A45,A42,A39,A36,A33,A30,A27,A24,A21,A18,A15,A12,A9A6=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,D9D6=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,A11A8=CEILING((B5+"0:05"),"00:05")+(IF(D5="Flight","0:10"))Cells with Data ValidationCellAllowCriteriaD50:D52List=Sheet2!$F$2:$F$27D5List=Sheet2!$A$1:$A$48D8List=Sheet2!$A$1:$A$48D11List=Sheet2!$A$1:$A$48D14List=Sheet2!$A$1:$A$48D17List=Sheet2!$A$1:$A$48D20List=Sheet2!$A$1:$A$48D23List=Sheet2!$A$1:$A$48D26List=Sheet2!$A$1:$A$48D29List=Sheet2!$A$1:$A$48D32List=Sheet2!$A$1:$A$48D35List=Sheet2!$A$1:$A$48D38List=Sheet2!$A$1:$A$48D41List=Sheet2!$A$1:$A$48D44List=Sheet2!$A$1:$A$48D47List=Sheet2!$A$1:$A$48

Daily Schedule 2023 v4 -ETAF.xlsxABCDEF1EVENT TYPECATEGORYDURATIONRON2DAILY SENIOR STAFF BRIEFINGxxxxx receives his Daily Senior Staff BriefingFLIGHT TIMEHome3AFTERNOON TEAxxxxx host an Afternoon Tea at2DRIVE TIMEHotel4ARRIVExxxxx arrive at 25AWARD GALAxxxxx attend an Award Gala at 26AWARDS CEREMONYxxxxx attend an Awards Ceremony at 27BREAKFASTxxxxx attend a breakfast at 28BRIEFINGxxxxx attend a Briefing at 29CEREMONYxxxxx attend a Ceremony at 210CHURCH SERVICExxxxx attend a Church Service at 211COMMEMORATIVE SERVICExxxxx attend a Commemorative Service at 212DINNERxxxxx attend a Dinner at 213EVENTxxxxx attend an Event at 214FUNERAL SERVICExxxxx attend a Funeral Service at 215GARDEN PARTYxxxxx host a Garden Party at 216LUNCHxxxxx attend a Lunch at 217MEETINGxxxxx attend a Meeting at 218MEMORIAL SERVICExxxxx attend a Memorial Service at 219MORNING TEAxxxxx attend a Morning Tea at 220PERFORMANCExxxxx attend a Performance at 221RECEPTIONxxxxx attend a Reception at 222SCHOOL VISITxxxxx attend a School Visit of 223TRAVELxxxxx Travel to 224VISITxxxxx Visit 225BALLxxxxx attend the 126FESTIVALxxxxx attend the 127VIDEO RECORDINGxxxxx Record a Video for 128CARxxxxx depart 29DEPARTxxxxx depart 30FLIGHTxxxxx depart 31RELAX TIMExxxxx have no events scheduled during this time32DOWN TIMExxxxx have no public events scheduled33Sheet2Cells with Conditional FormattingCellConditionCell FormatStop If TrueA2:C32Other TypeColor scaleNO


----------

