INDEX/MATCH Partial Match
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:
Best Answer
-
Does this work?
=INDEX(COLLECT({Tech Level}, {Description}, CONTAINS(@cell, Description@row)), 1)
Answers
-
@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","")))
Certified Platinum Partner
-
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)
-
Does this work?
=INDEX(COLLECT({Tech Level}, {Description}, CONTAINS(@cell, Description@row)), 1)
-
Yes it did. Thanks again!
-
Happy to help. 👍️
-
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), " ")
-
I don't see any rows in the Master that contain Description@row.
-
They say "Genesis AI 24" in both master and formula sheet
-
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.
-
Ah I see now. Thanks again.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 142 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 83 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 489 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!