Finding a lookup value within a string of text, if true, returning value in another column

[Excel Chimp]

I need to lookup the codes in the parentheses, and if present, return the value in column E. The values would be returned in a summary table on another worksheet.

As an example: Lookup "searbora" text in column A. If found in the cell, return the value located in column E.

Thanks so much.

Excel Workbook
	A	B	C	D	E
1	Work Order Status
2	For Selected Properties
3	Property	Call	In Progress	Scheduled	Total
4	example text(searbora)	21	0	0	21
5	example text number two(seashcro)	1	0	0	1
6	example text three(seaveash)	10	0	0	10
work order (2)

Excel 2010

 

[Aladin Akyurek]

I need to lookup the codes in the parentheses, and if present, return the value in column E. The values would be returned in a summary table on another worksheet.

As an example: Lookup "searbora" text in column A. If found in the cell, return the value located in column E.

Thanks so much.

Excel Workbook
	A	B	C	D	E
1	Work Order Status
2	For Selected Properties
3	Property	Call	In Progress	Scheduled	Total
4	example text(searbora)	21	0	0	21
5	example text number two(seashcro)	1	0	0	1
6	example text three(seaveash)	10	0	0	10
work order (2)

Excel 2010

Sheet2, A2: searboro

Try...

=INDEX(Sheet1!$E$4:$E$6,MATCH("*"&A2&"*",Sheet1!$A$4:$A$6,0))

 

[Excel Chimp]

Brilliant! I hadn't even considered the possibility of wildcard characters. I'm going to give this a try tomorrow. Sorry for my delayed reply and thanks so much!

 

[T. Valko]

I need to lookup the codes in the parentheses, and if present, return the value in column E. The values would be returned in a summary table on another worksheet.

As an example: Lookup "searbora" text in column A. If found in the cell, return the value located in column E.

Thanks so much.

Excel Workbook
	A	B	C	D	E
1	Work Order Status
2	For Selected Properties
3	Property	Call	In Progress	Scheduled	Total
4	example text(searbora)	21	0	0	21
5	example text number two(seashcro)	1	0	0	1
6	example text three(seaveash)	10	0	0	10
work order (2)

Excel 2010

Here's another one.

Assuming the lookup property will be unique.Sheet1<table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:30px; " /><col style="width:243px;" /><col style="width:30px;" /><col style="width:81px;" /><col style="width:75px;" /><col style="width:41px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">Property</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Call</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">In Progress</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Scheduled</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Total</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">example text(searbora)</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">21</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">21</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">example text number two(seashcro)</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">example text three(seaveash)</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">10</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">10Sheet2<table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:30px; " /><col style="width:72px;" /><col style="width:72px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">Property</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Total</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">searbora</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">21
Formula entered in B2:

=SUMIF(Sheet1!A4:A10,"*"&A2&"*",Sheet1!E4:E10)

 

[Aladin Akyurek]

Brilliant! I hadn't even considered the possibility of wildcard characters. I'm going to give this a try tomorrow. Sorry for my delayed reply and thanks so much!

You are welcome. Thanks for providing feedback.

 

[Excel Chimp]

Thank you Bill, much appreciated.

 

[T. Valko]

Thank you Bill, much appreciated.

You're welcome. Thanks for the feedback!