Look up sheet and partial matches

jjbennett

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

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.

Help?

Mark

jjbennett

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

ULXD4Q

ULXD4

ULXD4D

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

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.

Mark