Vlookup table array with strings
Trying to do get value from another sheet that contains commas and spaces but the Index formula does not work, do you have any other suggestion?
Best Answer
-
INDEX is probably the formula you want to use, can you explain why it does not work? What does it do?
It looks like sheet 1 Product code contains one code while sheet 2 contains multiple codes. Is the issue that you are trying to match a cell with one code against a cell with many codes in and therefore there is no match?
If so, using INDEX with COLLECT and CONTAINS will help.
=INDEX(COLLECT({Product Status Log Comments}, {Product Status Log Codes}, CONTAINS(@cell, [Master Code]@row)), 1)
Note - I do not know which column you have called Range 3 and Range 4 so have included the column names in my cross-sheet references - you will need to adjust those.
Answers
-
INDEX is probably the formula you want to use, can you explain why it does not work? What does it do?
It looks like sheet 1 Product code contains one code while sheet 2 contains multiple codes. Is the issue that you are trying to match a cell with one code against a cell with many codes in and therefore there is no match?
If so, using INDEX with COLLECT and CONTAINS will help.
=INDEX(COLLECT({Product Status Log Comments}, {Product Status Log Codes}, CONTAINS(@cell, [Master Code]@row)), 1)
Note - I do not know which column you have called Range 3 and Range 4 so have included the column names in my cross-sheet references - you will need to adjust those.
-
Thank you, your formula worked perfectly!
-
Good to hear. Thanks for letting me know.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 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!