Search code examples
excelexcel-formulaconditional-statementsgoogle-sheets-formulastring-formatting

How do I return True if a string in a cell is contained anywhere in a column?

I am working in google sheets. Excel also works.

I have column called "funds." This column has names of venture funds, such as Accel, A16z, and Sequoia. One fund in each row.

I have another column called "funds2." This column also has names of venture funds, such as Accel, A16z, and Sequoia. The difference is that this column has multiple funds in each row. So each row looks like this: "Accel, Sequoia, A16z." Another row might have "Greylock, Primary Ventures, Union Square Ventures" and so on.

How do I write a formula to flag cases if any fund name in the column "funds" is found anywhere in "funds2" column?

Much thanks!

Solution

  • If I'm understanding your problem correctly, you can simply use COUNTIF with wildcards.

    Assuming 'funds' is in A2:A and 'funds2' is in B2:B, you can try the following formula in row 2 of an empty column:

    =MAP(A2:A,LAMBDA(fund,IF(fund="",,0<COUNTIF(B2:B,"*"&fund&"*"))))