Seamus2626
asked on
Match/Index adjustment
Hi,
I have some formula searching for "FCC" in column D, it then matches the date in row 3 and whether an attendee has attended a meeting
=IF(INDEX(INDEX(INDEX($D$5 :$D$61,MAT CH(LEFT($C 66,FIND(" ",$C66)-1),$A$5:$A$61,0)): $AU$61,0,M ATCH(D$63, $D$3:$AU$3 ,0)+1),MAT CH("FCC",I NDEX($C$5: $C$61,MATC H(LEFT($C6 6,FIND(" ",$C66)-1),$A$5:$A$61,0)): $C$61,0))= "Yes","Yes ","No")
Can i adjust the formula so, the formula looks for "FCC" within the text
So if i had
"Global Head of FCC - MWBR" in column D, then it would return that as a match and indicate the record had attended the meeting
Currently it will only recognise FCC, if that is all the cell contains
Many thanks
Seamus
I have some formula searching for "FCC" in column D, it then matches the date in row 3 and whether an attendee has attended a meeting
=IF(INDEX(INDEX(INDEX($D$5
Can i adjust the formula so, the formula looks for "FCC" within the text
So if i had
"Global Head of FCC - MWBR" in column D, then it would return that as a match and indicate the record had attended the meeting
Currently it will only recognise FCC, if that is all the cell contains
Many thanks
Seamus
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Teylan is there a way, my cell can contain the word "FCC" but display as "Global Head of FCC - RBWM"
This would be a workaround
This would be a workaround
This is the formula I helped you with earlier....
As Teylyn mentioned, here is the formula use the * wildcard.
I also noticed that you didn't revise the range limit from column AU to column BA (that is why you got #N/A errors)
In D66
=IF(INDEX(INDEX(INDEX($D$5 :$D$61,MAT CH(LEFT($C 66,FIND(" ",$C66)-1),$A$5:$A$61,0)): $AU$61,0,M ATCH(D$63, $D$3:$AU$3 ,0)+1),MAT CH("*FCC*" ,INDEX($C$ 5:$C$61,MA TCH(LEFT($ C66,FIND(" ",$C66)-1),$A$5:$A$61,0)): $C$61,0))= "Yes","Yes ","No")
As Teylyn mentioned, here is the formula use the * wildcard.
I also noticed that you didn't revise the range limit from column AU to column BA (that is why you got #N/A errors)
In D66
=IF(INDEX(INDEX(INDEX($D$5
ASKER
Hi NBVC,
If you put your formula into cell D66 and copy down D67, it returns "No" for attendance, which is not true, the wildcard seems to affect the result
Without the wildcard, it returns the correct answer "Yes"
I dont understand what is happening. Your thoughts would be appreciated!
Thanks
If you put your formula into cell D66 and copy down D67, it returns "No" for attendance, which is not true, the wildcard seems to affect the result
Without the wildcard, it returns the correct answer "Yes"
I dont understand what is happening. Your thoughts would be appreciated!
Thanks
For Jan 20th you will note that the Global Head of FCC for the "Attended" column for each LoB subgroup is either "No" or "n/a". There are no "YES" (for Jan 20 Attended). My formula returns No for No or N/a.
ASKER
Hi NBVC, theyre not mutually exclusive, as in, if either an FCC or a Global Head of FCC have attended, then return YES
Does that make sense?
Does that make sense?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect!
ASKER
The relevant formula is in D66:Y69
Many thanks
FCC-RAM-Workstream-WG-Attendance.xlsx