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

B

BeeDub57

New Member
Joined
Mar 8, 2019
Messages
1
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Hi,

B1 formula copied down:


Book1
ABCD
110 N Trauma 10 [201965] * James Barbra [0048668]0.4410 N Trauma 100.44
210 N Trauma 10 [201965] * James Barbra [0048668]0.4410 N Trauma 200.82
310 N Trauma 10 [201965] * James Barbra [0048668]0.4410 N Trauma 300.72
410 N Trauma 10 [201965] * James Barbra [0048668]0.4410 N Trauma 400.52
510 N Trauma 10 [201965] * James Barbra [0048668]0.4410 N Trauma 500.77
610 N Trauma 10 [201965] * James Barbra [0048668]0.4410 N Trauma 600.58
710 N Trauma 10 [201965] * James Barbra [0048668]0.4410 N Trauma Admin0.82
810 N Trauma 10 [201965] * James Barbra [0048668]0.44100 Oaks Admin1
910 N Trauma 10 [201965] * James Barbra [0048668]0.44100 Oaks Clinic Pharmacy1
1010 N Trauma 10 [201965] * James Barbra [0048668]0.44100 Oaks Phlebotomy0.65
1110 N Trauma 10 [201965] * James Barbra [0048668]0.441st Shift0.92
1210 N Trauma 10 [201965] * James Barbra [0048668]0.442nd Shift0.83
1310 N Trauma 10 [201965] * James Barbra [0048668]0.44340B Multi Cont0.9
1410 N Trauma 20 [201966] * Abigail Wardlaw [0107904]0.823MCN General Medicine1
1510 N Trauma 20 [201966] * Abigail Wardlaw [0107904]0.823MCN General Medicine Admin0.5
1610 N Trauma 20 [201966] * Abigail Wardlaw [0107904]0.823rd Shift1
1710 N Trauma 20 [201966] * Abigail Wardlaw [0107904]0.823RW Cohort 100.76
1810 N Trauma 20 [201966] * Abigail Wardlaw [0107904]0.823RW Cohort 200.94
1910 N Trauma 20 [201966] * Abigail Wardlaw [0107904]0.823RW Inpatient Medicine0.83
2010 N Trauma 20 [201966] * Abigail Wardlaw [0107904]0.824 East Obstetrics0.87
Sheet635
Cell Formulas
RangeFormula
B1=IFERROR(LOOKUP(2,1/SEARCH(C$1:C$20,A1),D$1:D$20),"")
 
Upvote 0
You're welcome, welcome to the forum, thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Index/Match - Return value if partial text from cell contained anywhere in separate column
Replies
5
Views
4K
excelnewb221
E
E
  • Solved
Index/Match - Return value if partial text from cell contained in separate column
2
Replies
16
Views
4K
Kevin_Li
Kevin_Li
T
  • Question Question
Search cell return value from another cell
Replies
9
Views
541
Fluff
Fluff
F
  • Locked
  • Question Question
How to get data from specific cell and to find a value in a table & pull a whole table in other sheet.
Replies
1
Views
318
Fluff
Fluff
L
  • Solved
Find Specific Text from Data Source - If cell contains certain wording have a different output.
Replies
4
Views
1K
LikeablePringle
L

Forum statistics

Threads
1,224,815
Messages
6,181,136
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top