Excel find text within cell in a range of cells.
Posted on 2013-05-21
I can't find an answer to my exact situation here or elsewhere. It's pretty simple, here's an example:
(row 1 and col 1 are the spreadsheet row/column)
1 bird one
2 bird dog two
4 (text to lookup) (VLOOKUP function)
I would like to use a vlookup function in cell B4 to find text in cell A4 within the range A1:B2 and report the value in column B. For example if A4 contains the value "bird", and I enter in B4 VLOOKUP(A4,A1:B2,2,0) and it returns the value I need - in B1 "one". But if I enter VLOOKUP("dog",A1:B2,2,0), it returns the #N/A error instead what I need, which is the value in B2 "two". I need the latter function to report the value in B2 = "two". In other words, find the text string WITHIN the cells in column A, not just the matching value. Is there a formula that will get me there or does it have to involve a macro? I much prefer a formula...