Match an item in a column with cells containing multiple items and return a value

jonarma

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?

Paul Newcome

Are you able to provide some screenshots for context?

jonarma

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.

Paul Newcome

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