Look up sheet and partial matches

I have a sheet with two columns [Model Number] and [Device Type]

What I want to do is populate the [Device Type] based on a partial match of the [Model Number]. For instance any Model Number with IPCP would return the Device Type as Controller. For example, all of these would return Controller in the device column. 

Extron IPCP 505

Extron IPCP Pro 250

Extron IPCP Pro 255

Extron IPCP Pro 555

Extron IPCP Pro PCS1

I currently have a formula (below) and look up sheet that serves this function but it only works for exact matches 

=IFERROR(INDEX({Short Desc}, MATCH([Model Number]@row, {Model}, 0)), "")

Can I have a look up sheet that returns a result based on a partial match? If so, how?


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 12/19/20

    Hi @jjbennett ,

    Try using the CONTAINS function. Syntax is:

    CONTAINS( search_for, range )

    Your formula would be:

    =IF(CONTAINS("IPCP", [Model Number]@row, "Contoller", /response if false/))

    You may need to do some modification if you are using it in combination with a lookup or other function.



    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Thanks, but your formula only works specifically for IPCP = Controller

    However I have a large number of Device Types and Model Numbers. Here are some other examples




    All need to return "Wireless Mic"

    Panasonic PT-MZ670

    Panasonic PT-MZ630

    Panasonic PT-RZ570

    Panasonic PT-RZ670

    Panasonic PT-RZ770

    All need to return "Projector"

    I have this working via a reference sheet, but it only works if the Model Number of the reference sheet is an exact match to the Model Number in the row. I need it to work if it is a partial match.

    =JOIN(COLLECT({Short Desc}, {Model Number}, FIND([Model Number]@row, @cell) > 0), ",")

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @jjbennett ,

    I thought that might be the case. The most accurate way to do this is to have your reference table contain all model numbers with their device type.

    You could try using VLOOKUP for a partial match. Create a referenced table that contains 2 rows: [Model #] and [Device Type]. Populate it with enough of the model number that it can be assigned to a device type. As an example, Use "Panasonic PT-" as the Model # and "Desk" as the {Device Type].

    VLOOKUP with a "TRUE" in the match type assumes that the range is sorted ascending and returns the nearest match that is less than or equal to ( <= ) search_value. That's the partial match you're looking for.

    If you get #NoMatch errors you need to either add different Model #S or add other options to your Vlookup until you find the right formula to give you the right answer every time. As an example, you could change your look up to be: =IFERROR(VLOOKUP(Model Number]@row, {Refence Table Reference}, 2, TRUE), VLOOKUP(LEFT(Model Number]@row, 4),{Reference Table Reference, 2, TRUE)). In this case if the full Model Number isn't found then it shortens the model number to the first 4 characters and tries again.

    Your reference table needs to be sorted alphabetically by model number. It should look something like:

    [MODEL#] [Device Type]

    Extron IPCP Controller

    Panasonic PT- Projector

    ULXD Wireless Mic

    Sorry I can't be of more help. Maybe someone else will have a better answer.


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!