How can I write an excel formula to return a value from a range based on the contents of a cell?

Question

How can I write an Excel formula which will check if a cell contains a word listed in a separate range, and then return the single, individual word from the specified range?

The formula must specify the cell and the range, rather than cramming the values into the formula itself.

Example:

For example, the formula is being written in cell B1 and references the cell A1. The cell A1 contains the text "uk car insurance". The range is located from cells C1 to cell C10 (C1:C10). In this range 10 countries are listed, one per cell cascading downwards (e.g: C1="uk", C2="usa", C3="france" etc).

Based on this example, the formula in B1 should pull back the value "uk" from the range of cells (C1:C10) as the cell in A1 contains the text "uk car insurance" which contains "uk". The text of "uk" is also stored in cell C1, so that's what should be pulled back into B1.

A formula like this (moving away from our example now) wouldn't work for my purposes:

=IF(COUNTIF(L5:L47,"FALSE"),"Portfolios Mismatched","Portfolios Matched")

My range of countries in column C may become vast and thus any formula which attempts to list the values within itself (instead of referring to a range) is not scalable.

Answer 1

Use INDEX and AGGREGATE:

=INDEX(C:C,AGGREGATE(15,6,ROW($C$1:INDEX(C:C,MATCH("zzz",C:C)))/(ISNUMBER(SEARCH(" " & $C$1:INDEX(C:C,MATCH("zzz",C:C)) & " "," " & A1 & " "))),1))

The $C$1:INDEX(C:C,MATCH("zzz",C:C)) sets the range, it is dynamic. It will set the range to only those with values and change as values are added or removed from the reference list.

The SEARCH will search for matches. The " " & and & " " make sure we are looking for the entire word, So we do not get false positives on things like eric and erica.

The Aggregate will find the first row in which a word if found in the reference cell.

Index returns that word as the answer.