Return a value, if a cell contains text from a range of options

Wayne Kung

Hi there!

I'm not sure if there's an easy way to do this, so I'll try to break this out into parts:

1) I have a table with two columns - one with various text options (for easy, let's call these "flavours"), the other with the "type" or category that these text options would fit under

2) On a separate sheet, I want to see if a cell contains one of the "flavours", and subsequently return the "type" associated with it

Basically, there are multiple "flavours" that could have the same "type" - I want to see if I can find one of those "flavours" within the text of a cell, and return the corresponding "type".

The only option I've come up with so far is to do a crazily-nested series of IF and CONTAINS functions, but I have a feeling there's any easier way to do it! Any ideas?

Thanks in advance!

Jeff Reisman

On your separate sheet (lets call it the metrics sheet,) list your flavours individually in the Flavour column. In your Type column, try something built like this:

=INDEX(COLLECT({1st Sheet Type range}, {1st Sheet Flavour range}, CONTAINS(Flavour@row), 1))

Wayne Kung

Thanks Jeff! Unfortunately, I don't think this one works in this case. The Flavour@row cells are basically like a one-to-many - in the CONTAINS function, they'd be more like where you're looking for the text, but there are multiple options that I'm trying to look for.

That's where I'm getting stuck - can I "reverse" a CONTAINS function, where you specify a range of values to look for, but the place you're looking at is only one cell?

Jeff Reisman

Let me see if I have this straight. This is kind of what your data looks like, and you want to pull values for Type based on the multiple values in the Flavour column?

Wayne Kung

In a way, yes! Let me take your sample data and tweak it a bit - the hope would be that the question marks would return whatever the corresponding Type is from Sheet 1, if it finds the flavour in the Text column

Jeff Reisman

OK, bear with me here:

What if you create hidden helper columns in Sheet 2, one for each flavour?

=IF(CONTAINS("Cherry", Text@row), 1, "")

=IF(CONTAINS("Apple", Text@row), 1, "")

=IF(CONTAINS("Grape", Text@row), 2, "")

etc.

In the Type column, create a JOIN to gather the value from your range of flavour columns:

=JOIN(Cherry@row:Grapefruit@row)

Then, replacing the ? in your chart above, would be:

Alternatively, since two flavours can have the same type, do a helper column for each pair of flavours:

=IF(OR(CONTAINS("Cherry", Text@row), CONTAINS("Apple", Text@row)), 1, "")

=IF(OR(CONTAINS("Grape", Text@row), CONTAINS("Blueberry", Text@row)), 2, "")

=IF(OR(CONTAINS("Orange", Text@row), CONTAINS("Grapefruit", Text@row)), 3, "")