Find text

[sajohnson05]

I am trying to find text from worksheet 2 and put the risk factor into worksheet 1. Any help would be great, not sure the best formula to use.

1

Area Served	Factor
*******	Forumla
MRI
Operating Room

<tbody>
</tbody>

2

Area Served	Risk Factor
Operating Rooms	5

<tbody>
</tbody>

 

[liveinhope]

assuming that
- the data is in columns A and B on both sheets , column header in row 1 and the data itself in rows 2 onwards
- sheets are called sheet1 and sheet2

formula in B2 then drag down is =VLOOKUP(A2,Sheet2!$A:$B,2,FALSE) .. result below. note that the 2nd row returns a result of N/A because you refer to "Operating Room" on sheet1 and "Operating Rooms" on shhet2
this is not an exact match so doesn't work.

Use the same term on both sheets and you will

Area Served	Factor
MRI	#N/A
Operating Room	#N/A

<tbody>
</tbody>

Use the same term on both sheets and you will get this result

Area Served	Factor
MRI	#N/A
Operating Rooms	5

<tbody>
</tbody>

Can you use the same term on both sheets

 

[sajohnson05]

Is there a way it will work if a certain text is in sheet 1 but the exact is not in sheet 2. Say in sheet 1 it say ICU but in sheet two it may say ICU/CCU.

 

[dUBBINS]

=VLOOKUP("*"&A2&"*",Sheet2!$A:$B,2,FALSE)

Does this do what you want?

 

[liveinhope]

try this then =VLOOKUP(CONCATENATE("*",A2,"*"),Sheet2!$A:$B,2,FALSE)

uses * as wild card so on the "operating room" row looks for *Operating Room* on sheet 2 .

unfortunately this wont work if sheet1 has the longer text . eg in my example I have added a row "Car Parks" on shheet1. On sheet2 I have just called it "Car Park" so the VLOOKUp with wildcard still returns n/A
If at all possible would be a good idea to standardize the terminology

Area Served	Factor
MRI	#N/A
Operating Room	5
Car Parks	#N/A

<colgroup><col><col></colgroup><tbody>
</tbody>