Index, Match with a Contains.
Hi,
I normally index match to return a value from a table but this only works for the match section if it is a 1 for 1 scenario. The scenario i currently have is the reference table which i'm indexing from for the field i am using as a match has additional information (See Table 2). How do i collect the information from Table 2 if it has additional information. I have tried to introduce a contains in but i get an error.
Thanks in advance for your help.
Answers
-
@Michael Foster Are you trying to return information from two (or more) columns? Or are you trying to narrow what is returned by comparing more than one column? (Sorry, I can't tell exactly what your objective is.)
dm
-
@Dale Murphy I'm trying to Collect "Mr Smith" from Table 2 to Table 1, I only have a partial match as table 1 only has "FC" but table 2 shows "FC / DC". I want the formula to use "FC" from table 1 code field and see if it is contained in table 2 code field. If it is it should bring back the data from Table 2 Index Match to Table 1 Collect information.
Thanks
MF
-
Try looking into an INDEX/COLLECT combo with a CONTAINS.
=INDEX(COLLECT({Range to pull}, {Range to Match}, CONTAINS(@cell, Code@row)), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!