Tips for Matching Prospects with VC Partners in Excel

Hi everyone! I am trying to take a list of prospects and figure out which of them have taken investment from VC partners who we’ve established partnerships with. I have two table in a single workbook: one for prospects and one for our VC partners. I’ve created a column in the Prospects table called ‘Investors’ which is just a list of the VCs who have invested in the company across all funding rounds. Does anyone have ideas on how I can create another column that runs the ‘Investors’ column up against our VC partners list & pulls out investors from that list?

9 comments

Tanvi R.
·
·
Hey Alex, thanks for reaching out! You can use the “Lookup Single Row in Other Table” enrichment to handle this. It’s free in Clay, and once you add your Clay API Key, you’ll choose the table to search and the value to match. When a match is found, you can add it to your new table. https://downloads.intercomcdn.com/i/o/1174297413/4019525c61986a94eca5c350/CleanShot+2024-09-09+at+15_37_49%402x.png?expires=1725912900&signature=849883f75bd8363fc7ef3a9a5e542e53db5d924c8fa3d42aab58d30023e90d85&req=dSEgEst3moVeWvMW1HO4zcwyiQ33B2I%2BskTnrJqYFhicJ3lRqyZ6jkNjDS7o%0AsFYM%0A https://downloads.intercomcdn.com/i/o/1174305528/bd185f37ed85793e3a51b7cf/CleanShot+2024-09-09+at+15_42_10%402x.png?expires=1725912900&signature=a2e79717e05887c4a58b32fa32e835c81d600e5ea6a9029636ac4b5df42eaf29&req=dSEgEsp%2BmIRdUfMW1HO4zSmZgNOKOxtGBeSCEVlsBKRb0M4a3PJar9bPtk03%0ALJwh%0A https://downloads.intercomcdn.com/i/o/1174305552/787b4f59a1ca6309c023782d/CleanShot+2024-09-09+at+15_42_31%402x.png?expires=1725912900&signature=fe622676990f8c65f468ba6dcbc04a7abf6632fc03b2f4e243511ddb070293e9&req=dSEgEsp%2BmIRaW%2FMW1HO4zSs%2BnfROfS7zRaILJiWB0KCFAnkfL8m4Bhbhn60%2F%0A3YsL%0A Check out this tutorial for a quick walkthrough: https://www.loom.com/share/ad767e3be9914d19a2f6647efe4dfa85?sid=e2f7623b-91c5-46c7-bd58-67a24081593d.
Alex W.
·
·
Thanks for the reply Tanvi R.! I’ve tried this, but my issue is a little more complicated than a simple lookup. I have a column that contains a comma-separated list of all the company’s investors, and I need to check whether any part of that column’s string is contained in a row in our VC Partners table. Example: a company’s Investors column might be “Matrix Partners, Passport Capital, Bow Capital, General Catalyst” and I need the lookup to check whether ‘Matrix Partners’ OR ‘Passport Capital’ OR ‘Bow Capital’ OR ‘General Catalyst’ is listed in our VC Partners DB.
Bo (.
·
·
Ah, you’re right! Thanks for adding more details—it’s super helpful. Here’s what I suggest: You’ll need to extract the values as individual rows. I recommend converting your current table into a workbook. From there, use the “Extract values from data” option and apply a custom separation using a regular expression (regex). The regex to enter is [^,]+, which will separate all the values based on the commas. Next, map all these extracted values and use a “write to table” action to send them into a new table. Make sure to add any additional data you want to retain from the original table (e.g., the domain associated with each value). Once you’ve sent everything to the new table, you can add a formula to remove any quotation marks, as the regex will likely leave some behind. Finally, you can run a lookup against that table. This approach should help solve the issue! 😊 PS next time please send your table URL so we can proceed to help you better and faster :)
Alex W.
·
·
Bo (. this is extremely helpful, thanks so much! I was able to get the lookup working in a separate ‘Investor Data’ table. Last step - how can I roll the data from this Investor Data table back up in my main client table? Here’s the link to the workbook: https://app.clay.com/workspaces/194445/workbooks/wb_BdcNurqFkNdX/tables/t_gqN2ZMxTEm5s/views/gv_xZ9WMKwiZsdD
Bo (.
·
·
You would need to use a Lookup. For example, I see that you sent the account, then you can do a Lookup to that other table based on the Account.
Channeled
APP
·
·
We haven't heard back from you in a bit, so we're going to go ahead and close things out here - feel free to let us know if you still need something!
Channeled
APP
·
·
Hi Alex W.! This thread was recently closed by our Support team. If you have a moment, please share your feedback:
Alex W.
·
·
Sorry for the delay - can we please open this back up? I was able to lookup the records from the other table in my main table, but now I need to filter the full set of records based on a result of the investor lookup table. Essentially, I want to output a column in the main table that pulls only investors who were found to be VC partners. There is a field in each record pulled called ‘VC Partner?’ and I’d like to filter on that. Here’s a link to the table again for reference, thanks for your support! https://app.clay.com/workspaces/194445/workbooks/wb_p2gGKDgsCE4U/tables/t_evenhXCMTcSZ/views/gv_ILsmHsNiy6BB
Owen C.
·
·
Hey Alex! No worries, thanks for reaching out! If everyone in the 'VC Partner Table' is indeed a VC partner, filtering the Investor Data table based on their 'VC Partner' status is super quick! I’ve included a quick GIF to walk you through it. Additionally, if you'd like to filter data based on something found through the lookup function, you can map that data to its own column and then follow the same steps. Did this answer your question completely? Feel free to reach out if you need any more help! 😊 https://downloads.intercomcdn.com/i/o/1199183105/4db2634cb835d9f78ee1bf57/CleanShot+2024-09-30+at+18_02_00.gif?expires=1727736300&signature=203db27d59617974123ec280262aa6eaff02c07595c66950f166543b8bad0e3a&req=dSEuH8h2noBfXPMW1HO4zQUh0h9HyZBdYbbmcRcSeclJmJt9PAA15t66q8c2%0A6YQ6%0A