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.
[TABLE="width: 800, align: left"]
<tbody>[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] [/TD]
[TD]10 N Trauma 10[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.44[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[TD][/TD]
[TD]10 N Trauma 20[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.82[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]10 N Trauma 30[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.72[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]10 N Trauma 40[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]10 N Trauma 50[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.77[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]10 N Trauma 60[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.58[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]10 N Trauma Admin[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.82[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]100 Oaks Admin[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]1.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]100 Oaks Clinic Pharmacy[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]1.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]100 Oaks Phlebotomy[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.65[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]1st Shift[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.92[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]2nd Shift[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.83[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]340B Multi Cont[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.90[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]3MCN General Medicine[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]1.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]3MCN General Medicine Admin[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]3rd Shift[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]1.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]3RW Cohort 10[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.76[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]3RW Cohort 20[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.94[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]3RW Inpatient Medicine[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.83[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]4 East Obstetrics[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.87[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
I've been trying:
=IF(ISNUMBER(SEARCH($C$1:C$1957,A1)),$D$1:$D$1957,"")
...but no luck.
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.
[TABLE="width: 800, align: left"]
<tbody>[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] [/TD]
[TD]10 N Trauma 10[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.44[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[TD][/TD]
[TD]10 N Trauma 20[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.82[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]10 N Trauma 30[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.72[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]10 N Trauma 40[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]10 N Trauma 50[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.77[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]10 N Trauma 60[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.58[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]10 N Trauma Admin[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.82[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]100 Oaks Admin[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]1.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]100 Oaks Clinic Pharmacy[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]1.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]100 Oaks Phlebotomy[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.65[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]1st Shift[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.92[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]2nd Shift[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.83[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 366"]
<tbody>[TR]
[TD="width: 366"]10 N Trauma 10 [201965] * James Barbra [0048668][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]340B Multi Cont[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.90[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]3MCN General Medicine[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]1.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]3MCN General Medicine Admin[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]3rd Shift[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]1.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]3RW Cohort 10[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.76[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]3RW Cohort 20[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.94[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]3RW Inpatient Medicine[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.83[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10 N Trauma 20 [201966] * Abigail Wardlaw [0107904][/TD]
[TD][/TD]
[TD]4 East Obstetrics[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88, align: right"]0.87[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
I've been trying:
=IF(ISNUMBER(SEARCH($C$1:C$1957,A1)),$D$1:$D$1957,"")
...but no luck.