Partial Match for a Vlookup


Hi All,

I am looking for some help with a formula. I want to search for a particular value in a cell that may include multiple values i.e I want to locate A123 but the cell from the reference sheet contains A123; B456.

My thought was to use a partial vlookup but from what I have read it does not look like wildcards can be used and I haven't been able to leverage the contains function either.

Would anyone have any suggestions?



  • Tim Shaded
    Tim Shaded ✭✭✭✭

    This sounds like it should be simple enough to accomplish with something like this:

    =COUNTIF({Reference Sheet Column}, CONTAINS([Value Searched For]@row, @cell))

    Reference Sheet Column - represents a reference from another sheet which is being audited for entries.

    Value Searched For - represents a column on the same sheet where the formula is entered for tabulation. In that column each row would allow for entry of the appropriate value being searched for (ie. A123).

    COUNTIFS can be used if multiple criteria need to be met.

  • Jonathon Kavanagh

    Thanks Tim, is there a way to go one step further and pull back a different data point from the reference sheet i.e. I want to look for A123 in column A which contains A123; B456 and then return the value in column B?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!