arguement problem

[chubbybubba1]

I have the formula below look in cell "J100" of another workbook, then within a worksheet. If it finds "KOMEN", it takes the text in row A100 of that worksheet and gives me the entered text. That is working great. What if I want it to look for other known entered words in J100:J5000? What I'm saying is that various data entry clerks have entered "Komen" 2 other ways. CICP/KOMEN and CHS/KOMEN. How do I continue my formula to look for all 3 ways in one formula. Commas between the KOMEN's aren't working. I guess I figured it would import any hit with "KOMEN", but it doesn't.

Thanks for any advice.

=IF('[WMS LOG SHEET1.xls]06-30-05 through 06-29-06'!J100="KOMEN",'[WMS LOG SHEET1.xls]06-30-05 through 06-29-06'!A100,"")

 

[just_jon]

I have the formula below look in cell "J100" of another workbook, then within a worksheet. If it finds "KOMEN", it takes the text in row A100 of that worksheet and gives me the entered text. That is working great. What if I want it to look for other known entered words in J100:J5000? What I'm saying is that various data entry clerks have entered "Komen" 2 other ways. CICP/KOMEN and CHS/KOMEN. How do I continue my formula to look for all 3 ways in one formula. Commas between the KOMEN's aren't working. I guess I figured it would import any hit with "KOMEN", but it doesn't.

Thanks for any advice.

=IF('[WMS LOG SHEET1.xls]06-30-05 through 06-29-06'!J100="KOMEN",'[WMS LOG SHEET1.xls]06-30-05 through 06-29-06'!A100,"")

Try --

IF(ISNUMBER(MATCH('[WMS LOG SHEET1.xls]06-30-05 through 06-29-06'!J100,A1:A3,0)),'[WMS LOG SHEET1.xls]06-30-05 through 06-29-06'!A100,"")

Where A1:A3 hold the possible matches.

Unchecked as to syntax typos.

 

[barry houdini]

Perhaps

=IF(ISNUMBER(SEARCH("KOMEN",'[WMS LOG SHEET1.xls]06-30-05 through 06-29-06'!J100)),'[WMS LOG SHEET1.xls]06-30-05 through 06-29-06'!A100,"")

this should return A100 if "KOMEN" is found anywhere in J100 (in upper or lower case)

 

[chubbybubba1]

Both work equally well! Thank you so much.