VLOOKUP for cells that contain the search value but may not have an exact match?
I would like to use VLOOKUP to automatically fill cells with information from one sheet to another. On one sheet, each row of data represents one event. The other sheet (referenced by VLOOKUP) is for reserving resources for the event.
Sheet A:
Sheet B (referenced by VLOOKUP):
The issue I'm having is that sometimes multiple events will use the same resources on the same day, meaning in Sheet B we have multiple "program codes" listed for one row of data. Program codes are never repeated - one program code will only ever be associated with one row of data on Sheet B.
My issue is that VLOOKUP doesn't actually work with my setup because that function looks for exact matches instead of looking for cells that contain the search value. I was trying to think of ways to combine the CONTAINS, IF, and VLOOKUP functions, but I can't figure out how to do that and actually get what I need.
Does anyone have suggestions? Thanks!
Best Answer
-
Try an INDEX/COLLECT...
=INDEX(COLLECT({Range to Pull}, {Program Codes Column}, CONTAINS([Program Code]@row, @cell)), 1)
Answers
-
Try an INDEX/COLLECT...
=INDEX(COLLECT({Range to Pull}, {Program Codes Column}, CONTAINS([Program Code]@row, @cell)), 1)
-
@Paul Newcome thank you so much! This worked perfectly!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!