# arguement problem

#### chubbybubba1

##### New Member
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.

=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,"")

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
chubbybubba1 said:
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.

=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.

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)

Both work equally well! Thank you so much.

Replies
7
Views
211
Replies
4
Views
125
Replies
5
Views
207
Replies
2
Views
184
Replies
6
Views
253

1,203,465
Messages
6,055,574
Members
444,799
Latest member
CraigCrowhurst

### 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.

### Which adblocker are you using?

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

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