Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Match/Find

in the attached ss i am trying to track attendees to a meeting.

I need to match the names and the dates and return YES/NO

I have highlighted in yellow the names that need looking up

Accountable Executive
Head of AML Models
 
are one to one, straight matches, for the rest you need to find FCC in the appropriate range

So BMC [C22:C30] returns "no" as the FCC did not attend

So i suppose the find function would need to be utilised

Many thanks
Sam-Request.xlsx
0
Seamus2626
Asked:
Seamus2626
  • 2
  • 2
1 Solution
 
NBVCCommented:
so for are you checking only column C to find if there is a Yes in the Attended column for each date?  And for the other, you are checking first column A, then column C for FCC and then Yes in attended column?
0
 
Seamus2626Author Commented:
Exactly!

Thanks
0
 
NBVCCommented:
Ok, first, change the dates in row 3 to be real dates.  You can then format them as you wish them to appear.

Then in D64:

=IF(COUNTIFS($C$5:$C$61,$C64,INDEX($D$5:$AU$61,0,MATCH(D$63,$D$3:$AU$3,0)+1),"Yes")>0,"Yes","No")

copied to next row and across the columns

in D66:

=IF(INDEX(INDEX(INDEX($D$5:$D$61,MATCH(LEFT($C66,FIND(" ",$C66)-1),$A$5:$A$61,0)):$AU$61,0,MATCH(D$63,$D$3:$AU$3,0)+1),MATCH("FCC",INDEX($C$5:$C$61,MATCH(LEFT($C66,FIND(" ",$C66)-1),$A$5:$A$61,0)):$C$61,0))="Yes","Yes","No")

copied down and across the columns

Note:  You have set conditional formatting to "white out" any "No" values...
0
 
Seamus2626Author Commented:
Perfect!

Thanks :-)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now