Get data from another row - matching single value with multible dropdown values

Ida Lund

Hi, I have this big sheet (actually two, but I cannot even get it to work in one).

I want to return the value in column16, men I can find the value (doesn't have to be a multible select column) - WHEN I can find the single value within the multible-dropdown values.

I have searched and tried a lot, but I cannot get it to return the X/Y/Z-value - or not even just the values in the "model" row, when I find a match.

HELP

Best regards

Ida

SSFeatures

Hi Ida,

Try this formula:

=JOIN(COLLECT({Column16 Range}, {Column15 Range}, CONTAINS(@cell, Model@row)), ", ")

This does the following:

1. Return all of the values from Column16 (such as X, Y, Z) if they meet the following criteria:
2. Look at the data in Column15.
3. If the data in Column15 is found within the Model column then return the data from Column16.

I added a JOIN with a comma so that if multiple values are found, they are returned with a comma.

Hope this helps!

Best,

SSFeatures

Ida Lund

Yay, thank you so much - this works within the same sheet! :-) (EDIT: IT DID NOT WORK AS EXPECTED - SEE COMMENT BELOW)

I thought I could just "translate" it to work when referencing to another sheet.

I might have exlained something wrong, but this were the formula that worked for me:

=JOIN(COLLECT([Supplier 2]:[Supplier 2]; Modelnr:Modelnr; CONTAINS(@cell; Model@row)); ",")

BUT if the Model-row (used in the "contains"-fomula (the mulitble select-column)) is in another sheet. Is this possible to work with? Or is this just impossible…

Ida Lund

I will try to explain again.

I have sheet 1 (S1), where I have a multible drop-down list with model-numbers + a Supplier 1-column. Here I have the data I need in Sheet 2 (S2).

In sheet 2 I have a row with model numbers, WHEN these matches with model-numbers in S1, I need the supplier-name in the row from S1 to go into the supplier-column in S2.

This will help me, so I only need to adjust the suppliers in S1 and S2-suppliers will automaticly be updated.

Best regards

Ida