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. 👍️

Samantha S.

The formula has been working but I now have a new hearing aid on the list that it is not triggering for an I can't figure out why.

Master HA List:

Formula Sheet:

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

Paul Newcome

I don't see any rows in the Master that contain Description@row.

Samantha S.

They say "Genesis AI 24" in both master and formula sheet

Paul Newcome

Right, but the formula is looking through the referenced column for a cell that contains the full text. If you want to search for "Genesis AI 24" even though both cells have longer strings, you would need to specify that in the formula or in a separate column.

Samantha S.

Ah I see now. Thanks again.