Excel IF Formula

[tropics123]

Hi everyone, I need help with this formula. What I'm trying to do is, if the result from a different spreadsheet has the word:

1) "elapsed" anywhere in the sentence, then put in words "Elapsed Time"
2) "consecutive" anywhere in the sentence, then put in the words "Elapsed Time"
3) "hours" anywhere in the sentence, then put in the words "Hours of Service"
If none of those keywords are found, then return the answer "none". My formula is below. I think I'm close, but something is missing. Thank you in advance!

=IF(INDEX('Combined FTW database report'!2:1048576,MATCH('Plan Design'!$B$2,'Combined FTW database report'!$K$2:$K$1048576,0),MATCH("FKEligibleYOS",'Combined FTW database report'!1:1,0))="*elapsed*","Elapsed Time",IF(INDEX('Combined FTW database report'!2:1048576,MATCH('Plan Design'!$B$2,'Combined FTW database report'!$K$2:$K$1048576,0),MATCH("FKEligibleYOS",'Combined FTW database report'!1:1,0))="*consecutive*","Elapsed Time",IF(INDEX('Combined FTW database report'!2:1048576,MATCH('Plan Design'!$B$2,'Combined FTW database report'!$K$2:$K$1048576,0),MATCH("FKEligibleYOS",'Combined FTW database report'!1:1,0))="*hours*","Hours of Service","None")))

 

[COwen]

'Combined FTW database report'!2:1048576

What is this I have never seen it? What are the number referring to?

 

[HappierThan]

G'day 'tropics123', It looks to me as though you have left out something.....

'Combined FTW database report'!2:1048576

'Combined FTW database report'!$K$2:$K$1048576

 

[tropics123]

Hi, here's some background on the sheet that the formula is pulling from. Hope this will clear things up.

• The sheet is named "Combined FTW database report"
• "2:1048576" is the range starting on row 2
• Row 1 is the header

 

[tropics123]

I can simplify this so it won't be so messy. For example, based on the sheet below, I'm looking for "Bob" and if in column D:

1) has the word "elapsed" in it, then return this answer "Elapsed Time"
2) has the word "consecutive", then return this answer "Elapsed Time"
3) has the word "hours", then return this answer "Hours of Service"
4) If those keywords "elapsed, consecutive, hours" aren't found in column D for Bob, then return this answer "None"

=IF(INDEX('Combined FTW database report'!2:1048576,MATCH('Plan Design'!Bob,'Combined FTW database report'!$A$2:$A$1048576,0),MATCH("Type of Hours",'Combined FTW database report'!1:1,0))="*elapsed*","Elapsed Time",IF(INDEX('Combined FTW database report'!2:1048576,MATCH('Plan Design'!Bob,'Combined FTW database report'!$A$2:$A$1048576,0),MATCH("Type of Hours",'Combined FTW database report'!1:1,0))="*consecutive*","Elapsed Time",IF(INDEX('Combined FTW database report'!2:1048576,MATCH('Plan Design'!Bob,'Combined FTW database report'!$A$2:$A$1048576,0),MATCH("Type of Hours",'Combined FTW database report'!1:1,0))="*hours*","Hours of Service","None")))

Issue I have with the formula is, it returns the answer "none" no matter what.

Column A - Name	Column B - Date of Birth	Column C - Hours Worked	Column D - Type of Hours	Column E - Member	Column F -Type of Employment
Bob	1/2/1960	1000	Elapsed Time w/ Conditions	Yes	Part-Time
Mary	5/5/1987	2000	Hours of Service w/ Limit	Yes	Full-time
Woody	3/8/1999	500	Specific Hours	Yes	Part-time
Dylan	12/30/2000	890	Consecutive Hours	No	Part-time