Match an item in a column with cells containing multiple items and return a value
Hi,
Here is a brief description of the scenario.
There's a source file (Source) with a column (Item Group) having a variable number of items listed (possibly as multiple values column) and another column with specific values (Serial) for each row (or item group). Each item is listed only in one item group.
In another file - Database - there are all the items listed in a column (Items all) and I would like to pull the Serial data from the source file if the item is matched in the cell Item Group of the source file.
What's an efficient formula for that?
Answers
-
Are you able to provide some screenshots for context?
-
Here is an example of sheets that represent my case.
This is my source file, where I have multiple serial codes clustered in a cell. I have flexibility in changing how to insert them (separated by a comma or as multiple values).
Then I have my dashboard where I have all my item serials listed one per row. In the category (or price) column, I would like to have a formula that matches the item in the source file and completes it with the corresponding info.
Thanks for your help.
-
Give something like this a try... Change the Serial# column in the source sheet to be a multi-select dropdown then use this formula in the destination sheet:
=INDEX(COLLECT({Source Sheet Category Column}, {Source Sheet Serial# Column}, HAS([Item Serial]@row, @cell)), 1)
If that
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!