Hi,
Thank you so much for the formula. it did work for the smaller dummy data, but it's not working for the larger set of data.
I'm pasting the larger data for your reference below:
Mentor data:
Formula in the Mentor worksheet, last column:
IF(COLUMN()-COLUMN($L$1)>$I2,"",IFERROR(INDEX(Mentees!$B$2:$B$1048576,AGGREGATE(15,6,ROW(Mentees!$B$2:$B$1048576)-ROW(Mentees!$B2)/((IF(Mentees!$H$2:$H$1048576="Female",$D2="Female",TRUE))*(Mentees!$D$2:$D$1048576=$E2)*(Mentees!$I$2:$I$1048576=$J2)*(Mentees!$J$2:$J$1048576=$K2)*(Mentees!$K$2:$K$1048576=$L2)),COLUMN()-COLUMN($L$1))),""))
EID | Mentor | Email ID | Gender | Business Unit | Region | Time Zone | Business Unit Preference | How many mentees you want to mentor? | Lived Experience | What are the Professional Skills you have? | What are the Leadership Skills you have? |
E111111 | Joe Smith | joesmith@email.com | Male | Data and Services | AP | IST (India Standard Time) | No | 2 | Work/life integration | Business Acumen | Building self-awareness |
E222222 | Mary Brown | marybrown@gmail.com | Female | Cyber and Intelligence Solutions | NAM | EST (Eastern Standard Time) | No | 2 | LGBTQ | Consumer Experience | Building self-awareness |
E333333 | Rohit Arwala | rohit@email.com | Male | Operations and Technology | EUR | GMT (Greenwich Mean Time) | Yes | 1 | Parenting or caregiving | Business Acumen | Building self-awareness |
E444444 | Blaine Blue | bb@gmail.com | Male | North America | NAM | EST (Eastern Standard Time) | No | 1 | Relocation | Business Acumen | Providing consistent feedback and coaching |
E555555 | Maria Marna | maria@email.com | Female | International Markets | LAC | CST (Central Standard Time) | Yes | 1 | Relocation | Influence & Engagement | Building self-awareness |
E666666 | Anna King | anna@email.com | Female | Product & Engineering | EUR | GMT (Greenwich Mean Time) | No | 1 | Work/life integration | Business Acumen | Adaptability and flexibility/agility |
E777777 | John Martin | john@email.com | Male | International Markets | MEA | MET (Middle East Time) | No | 2 | Entrepreneurship | Business Acumen | Building self-awareness |
E088888 | Myrna Lebowitz | myrna@email.com | Female | Data and Services | EUR | EET (Eastern European Time) | Yes | 1 | Work/life integration | Influence & Engagement | Embracing and leading change |
Mentee Data:
EID | Full Name | Email ID | Business Unit | Region | Time Zone | Business Unit Preference | Gender Preference | Lived Experience | What are the Professional Skills you want to be mentored on? | What are the Leadership Skills you want to be mentored on? |
E123456 | Jenna Brown | jb@gmail.com | Strategic Growth | AP | CST (Central Standard Time) | Yes | No preference | Work/life integration | Business Acumen | Building self-awareness |
E098765 | Courtney Athina | ca@email.com | Law, Franchise, and Integrity | NAM | GMT (Greenwich Mean Time) | Yes | Female | Parenting or caregiving | Influence & Engagement | Building self-awareness |
E456456 | Anna Cho | ac@email.com | Operations and Technology | EUR | IST (India Standard Time) | Yes | No preference | Intersectionality | Business Acumen | Collaboration |
E124567 | Bree Lars | bl@gmail.com | International Markets | NAM | AGT (Argentina Standard Time) | No | Male | Work/life integration | Business Acumen | Building self-awareness |
E782310 | Monika White | mo@email.com | Cyber and Intelligence Solutions | LAC | EAT (Eastern African Time) | No | Male | Relocation | Business Acumen | Thoughtful risk taking |
E898920 | Jennifer Reigel | jr@gmail.com | Data and Services | EUR | EST (Eastern Standard Time) | No | Female | Different abilities | Complex Problem Solving | Embracing and leading change |
E674820 | Priti Khanal | pritik@email.com | Cyber and Intelligence Solutions | MEA | EST (Eastern Standard Time) | No | No preference | Work/life integration | Business Acumen | Providing consistent feedback and coaching |
E020301 | Jennie Torres | jt@email.com | International Markets | EUR | BET (Brazil Eastern Time) | Yes | Female | Different abilities | Creativity/Cognitive Flexibility | Providing consistent feedback and coaching |
Appreciate your help.