Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Match/Find

Posted on 2014-01-21
Medium Priority
262 Views
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

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
Question by:Seamus2626
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 23

Expert Comment

ID: 39797258
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

Author Comment

ID: 39797265
Exactly!

Thanks
0

LVL 23

Accepted Solution

NBVC earned 2000 total points
ID: 39797362
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

Author Closing Comment

ID: 39797424
Perfect!

Thanks :-)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month10 days, 16 hours left to enroll