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.