Forum Discussion

EdnaJohnson's avatar
EdnaJohnson
Copper Contributor
Jan 10, 2024

Match part of a cell and return value

I need to see if a cell matches any part of another cell in another sheet and return values based on if it's true or not. I have tried XLookup, CountIF, If, and many others. 

 

I believe it should be similar to:

=IF(TableName!B:B=TableName[ColumnName],"Y","N")

but I get the spill error. I am thinking if I added some type of any clause, it would limit the output? 

Thanks for any help or suggestions!!! 

 

 

 

 

 

 

excel
Formulas and Functions

6 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 10, 2024

    EdnaJohnson 

    Is TableName both a sheet and a table on another sheet?

    Perhaps

     

    =IF(COUNTIF(TableName!B:B, TableName[@ColumnName]), "Y", N")

     

    or

     

    =IF(COUNTIF(TableName!B:B, "*"&TableName[@ColumnName]&"*"), "Y", N")