INDEX/MATCH Partial Match

Samantha S.

I am trying to pull the tech level for a hearing aid based on a master list. The issue is that my source sheet has a simplified name of the hearing aid that is on the sheet I need a the tech level. I've tried multiple variations of INDEX MATCH and COLLECT and am not getting it to pull.

Formula Sheet:

Source Sheet:

Paul Newcome

Does this work?

=INDEX(COLLECT({Tech Level}, {Description}, CONTAINS(@cell, Description@row)), 1)

Matt Lynn-PCG

@Sam H. I think you'll need to use index(collect()) with a contains() inside the collect().

Something like =index(collect({TechLevel},{Description},contains("intent 3",@cell)),1)

OR… since your data for the criteria (i.e. Intent 3) is buried in a string within the formula sheet you'll end up needing to do an if statement and or a helper column to pull out the intent. Might be easier to just write a multi if statement that says

=if(contains("Intent 1",description@row),"Ultimate",if(contains("Intent 2",description@row),"Premium",if(contains("Intent 3",description@row),"Advanced","")))

Samantha S.

I originally planned a multi IF but the problem is I have a list of 198 potential models.

Tried this too and still no results:

=INDEX(COLLECT({Tech Level}, Description@row, CONTAINS({Description}, @cell)), 1)

Paul Newcome

Does this work?

=INDEX(COLLECT({Tech Level}, {Description}, CONTAINS(@cell, Description@row)), 1)

Samantha S.

Yes it did. Thanks again!

Paul Newcome

Happy to help. 👍️