QUESTION: Finding cell text in other cell text, returning value from third cell

[BeeDub57]

I need to determine if the individual cells in column A (A1:A17933) contain text from any of the cells in column C (C1:C1957), and if so, return the column C cell's corresponding value in column D (D1:D1957) to the column B cell next to the column A cell containing the column C cell text.

For instance, in the following sample table, B1:B13 would be filled with 0.44, while B:14:B20 would be filled with 0.82.

10 N Trauma 10 [201965] * James Barbra [0048668] <tbody> </tbody>		10 N Trauma 10	0.44 <tbody> </tbody>
10 N Trauma 10 [201965] * James Barbra [0048668]		10 N Trauma 20	0.82 <tbody> </tbody>
10 N Trauma 10 [201965] * James Barbra [0048668] <tbody> </tbody>		10 N Trauma 30	0.72 <tbody> </tbody>
10 N Trauma 10 [201965] * James Barbra [0048668] <tbody> </tbody>		10 N Trauma 40	0.52 <tbody> </tbody>
10 N Trauma 10 [201965] * James Barbra [0048668] <tbody> </tbody>		10 N Trauma 50	0.77 <tbody> </tbody>
10 N Trauma 10 [201965] * James Barbra [0048668] <tbody> </tbody>		10 N Trauma 60	0.58 <tbody> </tbody>
10 N Trauma 10 [201965] * James Barbra [0048668] <tbody> </tbody>		10 N Trauma Admin	0.82 <tbody> </tbody>
10 N Trauma 10 [201965] * James Barbra [0048668] <tbody> </tbody>		100 Oaks Admin	1.00 <tbody> </tbody>
10 N Trauma 10 [201965] * James Barbra [0048668] <tbody> </tbody>		100 Oaks Clinic Pharmacy	1.00 <tbody> </tbody>
10 N Trauma 10 [201965] * James Barbra [0048668] <tbody> </tbody>		100 Oaks Phlebotomy	0.65 <tbody> </tbody>
10 N Trauma 10 [201965] * James Barbra [0048668] <tbody> </tbody>		1st Shift	0.92 <tbody> </tbody>
10 N Trauma 10 [201965] * James Barbra [0048668] <tbody> </tbody>		2nd Shift	0.83 <tbody> </tbody>
10 N Trauma 10 [201965] * James Barbra [0048668] <tbody> </tbody>		340B Multi Cont	0.90 <tbody> </tbody>
10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]		3MCN General Medicine	1.00 <tbody> </tbody>
10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]		3MCN General Medicine Admin	0.50 <tbody> </tbody>
10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]		3rd Shift	1.00 <tbody> </tbody>
10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]		3RW Cohort 10	0.76 <tbody> </tbody>
10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]		3RW Cohort 20	0.94 <tbody> </tbody>
10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]		3RW Inpatient Medicine	0.83 <tbody> </tbody>
10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]		4 East Obstetrics	0.87 <tbody> </tbody>

<tbody>
</tbody>

I've been trying:

=IF(ISNUMBER(SEARCH($C$1:C$1957,A1)),$D$1:$D$1957,"")

...but no luck.

 

[jtakw]

Hi,

B1 formula copied down:

Book1
	A	B	C	D
1	10 N Trauma 10 [201965] * James Barbra [0048668]	0.44	10 N Trauma 10	0.44
2	10 N Trauma 10 [201965] * James Barbra [0048668]	0.44	10 N Trauma 20	0.82
3	10 N Trauma 10 [201965] * James Barbra [0048668]	0.44	10 N Trauma 30	0.72
4	10 N Trauma 10 [201965] * James Barbra [0048668]	0.44	10 N Trauma 40	0.52
5	10 N Trauma 10 [201965] * James Barbra [0048668]	0.44	10 N Trauma 50	0.77
6	10 N Trauma 10 [201965] * James Barbra [0048668]	0.44	10 N Trauma 60	0.58
7	10 N Trauma 10 [201965] * James Barbra [0048668]	0.44	10 N Trauma Admin	0.82
8	10 N Trauma 10 [201965] * James Barbra [0048668]	0.44	100 Oaks Admin	1
9	10 N Trauma 10 [201965] * James Barbra [0048668]	0.44	100 Oaks Clinic Pharmacy	1
10	10 N Trauma 10 [201965] * James Barbra [0048668]	0.44	100 Oaks Phlebotomy	0.65
11	10 N Trauma 10 [201965] * James Barbra [0048668]	0.44	1st Shift	0.92
12	10 N Trauma 10 [201965] * James Barbra [0048668]	0.44	2nd Shift	0.83
13	10 N Trauma 10 [201965] * James Barbra [0048668]	0.44	340B Multi Cont	0.9
14	10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]	0.82	3MCN General Medicine	1
15	10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]	0.82	3MCN General Medicine Admin	0.5
16	10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]	0.82	3rd Shift	1
17	10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]	0.82	3RW Cohort 10	0.76
18	10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]	0.82	3RW Cohort 20	0.94
19	10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]	0.82	3RW Inpatient Medicine	0.83
20	10 N Trauma 20 [201966] * Abigail Wardlaw [0107904]	0.82	4 East Obstetrics	0.87
Sheet635

Cell Formulas
Range	Formula
B1	=IFERROR(LOOKUP(2,1/SEARCH(C$1:C$20,A1),D$1:D$20),"")

 

[jtakw]

You're welcome, welcome to the forum, thanks for the feedback.