Vlookup formula that includes searching for specific criteria text within a group of cells

[copyboy007]

Is it possible to get a vlookup result if the specific text string (e.g., ww) is within a column of cells with multiple text strings? For example, how can the AA result show when looking in each row of the first column with multiple strings?

zz, yy	AA	A
xx, ww	BB	B
vv, uu	CC	C
tt, ss	DD	D
rr, qq	EE	E

 

[AhoyNC]

A few questions:
What version of Excel do you have?
If we use your example for "ww" won't the answer be "BB"?
Can there be duplicates ("ww" in more than one row)?

 

[AhoyNC]

Try:
I have made some assumption.

• ww should return BB
• There are no duplicates of the search item (ex. ww only shows in one cell in the column).

If this isn't what you want then please answer my questions in the post above.

Book1
	A	B	C	D	E
1	zz, yy	AA	A		BB
2	xx, ww	BB	B
3	vv, uu	CC	C
4	tt, ss	DD	D
5	rr, qq	EE	E
Sheet1

Cell Formulas
Range	Formula
E1	E1	=INDEX($B$1:$B$5,MATCH(TRUE,ISNUMBER(SEARCH(" "&"ww"," "&$A$1:$A$5&" ")),0))

 

[copyboy007]

Try:
I have made some assumption.

• ww should return BB
• There are no duplicates of the search item (ex. ww only shows in one cell in the column).

If this isn't what you want then please answer my questions in the post above.

Book1
	A	B	C	D	E
1	zz, yy	AA	A		BB
2	xx, ww	BB	B
3	vv, uu	CC	C
4	tt, ss	DD	D
5	rr, qq	EE	E
Sheet1

Cell Formulas
Range	Formula
E1	E1	=INDEX($B$1:$B$5,MATCH(TRUE,ISNUMBER(SEARCH(" "&"ww"," "&$A$1:$A$5&" ")),0))

Thanks for replying. Your assumptions are correct. The sample answer should be BB. I have Excel 2016, but when I duplicate the sample and solution, it's showing a '#N/A' result (value not available error). But, it looks like it should work.

 

[AhoyNC]

Did you change the ranges to match your actual data?
If so, what is the formula you used and what are the ranges your data is in?

 

[copyboy007]

Did you change the ranges to match your actual data?
If so, what is the formula you used and what are the ranges your data is in?

The ranges were updated to match, so I tried repeating the example in this thread using that formula to see if it might be a setting limitation with this work computer's setup.
=INDEX($B$1:$B$5,MATCH(TRUE,ISNUMBER(SEARCH(" "&"ww"," "&$A$1:$A$5&" ")),0))

zz, yy	AA	A		#N/A
xx, ww	BB	B
vv, uu	CC	C
tt, ss	DD	D
rr, qq	EE	E

 

[AhoyNC]

I copied your formula into my example and it works.
I don't think it should matter, but what version of Excel are you using?
When you tried to repeat my example did you paste it by clicking on what looks like 2 folders under the f(x) symbol and then pasting in cell A1?

Book1
	A	B	C	D	E
1	zz, yy	AA	A		BB
2	xx, ww	BB	B
3	vv, uu	CC	C
4	tt, ss	DD	D
5	rr, qq	EE	E
Sheet1

Cell Formulas
Range	Formula
E1	E1	=INDEX($B$1:$B$5,MATCH(TRUE,ISNUMBER(SEARCH(" "&"ww"," "&$A$1:$A$5&" ")),0))

 

[Asbestos_Jen]

Try entering the formula as an array (CTRL+SHIFT+ENTER) as I got #N/A when I tried it at first, but the array entry fixed it.

 

[copyboy007]

Try entering the formula as an array (CTRL+SHIFT+ENTER) as I got #N/A when I tried it at first, but the array entry fixed it.

The array entry works! Thank you to both of you.

 

[AhoyNC]

Glad we could help. Thanks for the feedback.
You should update your profile so we know what version of Excel you are using. As you can see it can make a difference.