Fill cells with text if cells in column have specific text string

[basicallychels]

I am trying to create something that will allow me to autofill the agency column with an Agency's name if the adjacent Facility Name cell contains a certain text string. So for example, if the Facility Name is Sacramento, Union City, Ventura, Fresno, Riverside, or Cypress I want to fill the adjacent Agency cell with VIC the PICC. But if the Facility Name is Glasgow, Louisville, Westlake, etc. I want to fill the adjacent Agency cell with Access RN. I have to do this each month, so if I could find something that would be easy to apply each month, like a macro, that would be awesome. I am just not sure what the best way to go about this is, since I have multiple agencies that go with multiple facilities.

Agency	Facility Name
VIC the PICC	SACRAMENTO
	HOUSTON

<tbody>
</tbody>

 

[ExcelMercy]

I have more than two agencies, I have 44 different agencies so unfortunately that won't work.

It can still be done, it certainly works better than doing it by manually each month.


=if(OR(A1="Sacramento",A1="Union City",A1="Ventura",A1="Fresno",A1="Riverside",A1="Cypress"),"VIC the PICC",IF(OR(A1="Glasgow", A1="Louisville", A1="Westlake")),"Access RN",IF(OR(A1="Portland", A1="Berlin", A1="Concord Warwick", A1="Greensurg")),"CIAs","")

You can keep repeating the IF(OR( until you get all your checks in.




Also you could do:

=IFERROR(INDEX($A$2:$A$14,MATCH(E2,$B$2:$B$14,0)),"")

Place in D2 and drag/click to fill in all the facility names.

	A	B	C	D	E
1	Agency_Types	Facility_Types		Agency	Facility Name
2	AccessRN	Bridgeton		AccessRN	Austin
3	AccessRN	Fridley		PICC Pros	El Paso
4	AccessRN	Lenexa		VIC the PICC	Sacramento
5	PICC Pros	Austin		AccessRN	Fridley
6	PICC Pros	El Paso		CIAs	Berlin
7	PICC Pros	Houston			etc
8	VIC the PICC	Sacramento			etc
9	VIC the PICC	Union City			etc
10	VIC the PICC	Fresno			etc
11	CIAs	Portland			etc
12	CIAs	Berlin			etc
13	QUICK PICC	Brockton			etc
14	IV QUEEN	Daphne			etc

<tbody>
</tbody>

If you want more Agency_Types / Facility_Types just manually add them in the Agency_Types / Facility_Types columns. If your agency/facility types remain pretty constant you shouldn't have to update or edit those fields once you've added them. Your actual data (Agency/Facility Name) will be automated.

 

[basicallychels]

Thanks, I totally get this now! The visual helped

 

[basicallychels]

Is there a way to include an exception, like if there was a third column for contract type, could I have it says enter Vic the picc if its sacramento, union city, ventura, etc but not if C1=monthly?

 

[ExcelMercy]

Sure,

=IF(C2="monthly","", IFERROR(INDEX($A$2:$A$14,MATCH(E2,$B$2:$B$14,0)),""))

 

[basicallychels]

Below is my formula, however it keeps giving me and error at Vinton. Do you know what is wrong?

=IF(OR(A1="NEWARK", A1="GRAND RAPIDS", A1="MILWAUKEE", A1=”WARREN”),"ACCESS RN",IF(OR(A1="BIRMINGHAM"),”ALABAMA PICC ",IF(OR(A1="VINTON",A1="GREENSBORO",A1="SIMPSON"),”CAROLINA WELNESS",IF(OR(A1="GREENSBURG", A1="HERMITAGE"),"CIAs",IF(OR(A1="VIRGINIA BEACH"),”INFUSION CARE SERVICES OF VA",IF(OR(A1="WARWICK "),”INFUSION SUPPORT SYSTEMS",IF(OR(A1="BELTSVILLE", A1="EASLEY", A1="LARGO", A1="LONGWOOD", A1="SARASOTA",A1="SMYRNA",A1="TALLAHASSEE"),"INSTITUTE OF NURSING EXCELLENCE”,IF(OR(A1="CHARLESTON"), “INTRAVENOUS EXPERT CONSULTANTS",IF(OR(A1="FRIDLEY"),"ITIVA",IF(OR(A1="FT LAUDERDALE"),"IV ACCESS",IF(OR(A1="KENT"),"IV NURSE CONSULTANTS",IF(OR(A1="NEWARK", A1="KING OF PRUSSIA"),"IV QUEEN",IF(OR(A1="HARRISBURG",A1="WILKES BARRE”),"MAIN LINE IV",IF(OR(A1="DAPHNE ",A1="JACKSON",A1="MEMPHIS"),"MID SOUTH PICC ",IF(OR(A1="KNOXVILLE"),"NURSE PRO PLUS ",IF(OR(A1="SALT LAKE CITY", A1="BOISE"),"NURSING SERVICES, INC.",IF(OR(A1="WAILUKU ",A1="HILO",A1="HONOLULU",A1="KAUAI"),"P&A PICC, LLC ",IF(OR(A1="GRAND PRAIRIE "),"PICCs PLUS, LLC ",IF(OR(A1="HOUSTON "),"PICC VIC, INC ",IF(OR(A1="SAN ANTONIO "),"PICC VIC RN ",IF(OR(A1="SIOUX FALLS",A1="DES MOINES",A1="OMAHA"),"VASCULAR ACCESS PLUS",IF(OR(A1="SACRAMENTO",A1="UNION CITY",A1="VENTURA",A1="FRESNO",A1="RIVERSIDE",A1="CYPRESS", A1="EL PASO",A1="SAN DIEGO ",A1="SAN LUIS OBISPO ",A1="STOCKTON",A1="TUCSON", A1="ALBUQUERQUE",A1="BROOMFIELD",A1="COLORADO SPRINGS",A1="MOUNTAIN VIEW"),"VIC the PICC"

 

[ExcelMercy]

You have a lot of different style quotes in there. ” needs to be "

Go to Find & Select -> Replace-> ” -> "

 

[basicallychels]

Am I replacing the direction of the quotes?

You have a lot of different style quotes in there. ” needs to be "

Go to Find & Select -> Replace-> ” -> "

 

[basicallychels]

It also is telling me, I have too many arguements

 

[ExcelMercy]

It also is telling me, I have too many arguements

To many arguments would mean your Ifs went past the 3 criteria required (logical test, value_if_true, value_if_false). I removed the problematic quotes and didn't get any argument errors.

See if this works:


=IF(OR(A1="NEWARK",A1="GRAND RAPIDS",A1="MILWAUKEE",A1="WARREN"),"ACCESS RN",IF(OR(A1="BIRMINGHAM"),"ALABAMA PICC ",IF(OR(A1="VINTON",A1="GREENSBORO",A1="SIMPSON"),"CAROLINA WELNESS",IF(OR(A1="GREENSBURG",A1="HERMITAGE"),"CIAs",IF(OR(A1="VIRGINIA BEACH"),"INFUSION CARE SERVICES OF VA",IF(OR(A1="WARWICK "),"INFUSION SUPPORT SYSTEMS",IF(OR(A1="BELTSVILLE",A1="EASLEY",A1="LARGO",A1="LONGWOOD",A1="SARASOTA",A1="SMYRNA",A1="TALLAHASSEE"),"INSTITUTE OF NURSING EXCELLENCE",IF(OR(A1="CHARLESTON"),"INTRAVENOUS EXPERT CONSULTANTS",IF(OR(A1="FRIDLEY"),"ITIVA",IF(OR(A1="FT LAUDERDALE"),"IV ACCESS",IF(OR(A1="KENT"),"IV NURSE CONSULTANTS",IF(OR(A1="NEWARK",A1="KING OF PRUSSIA"),"IV QUEEN",IF(OR(A1="HARRISBURG",A1="WILKES BARRE"),"MAIN LINE IV",IF(OR(A1="DAPHNE ",A1="JACKSON",A1="MEMPHIS"),"MID SOUTH PICC ",IF(OR(A1="KNOXVILLE"),"NURSE PRO PLUS ",IF(OR(A1="SALT LAKE CITY",A1="BOISE"),"NURSING SERVICES, INC.",IF(OR(A1="WAILUKU ",A1="HILO",A1="HONOLULU",A1="KAUAI"),"P&A PICC, LLC ",IF(OR(A1="GRAND PRAIRIE "),"PICCs PLUS, LLC ",IF(OR(A1="HOUSTON "),"PICC VIC, INC ",IF(OR(A1="SAN ANTONIO "),"PICC VIC RN ",IF(OR(A1="SIOUX FALLS",A1="DES MOINES",A1="OMAHA"),"VASCULAR ACCESS PLUS",IF(OR(A1="SACRAMENTO",A1="UNION CITY",A1="VENTURA",A1="FRESNO",A1="RIVERSIDE",A1="CYPRESS",A1="EL PASO",A1="SAN DIEGO ",A1="SAN LUIS OBISPO ",A1="STOCKTON",A1="TUCSON",A1="ALBUQUERQUE",A1="BROOMFIELD",A1="COLORADO SPRINGS",A1="MOUNTAIN VIEW"),"VIC the PICC"))))))))))))))))))))))

 

[basicallychels]

Before I saw your comment, I went ahead and did an index. However, I need to do two indexes in one cell because I need to use two different look up values. Can you help me make this work? It won't let me.

=IFERROR(INDEX(Sheet1!$C$27:$C$545,MATCH(E2,Sheet1!$B$27:$B$545,0)),""),IFERROR(INDEX(Sheet2!E3:E32,MATCH(A2,Sheet2!D3:D32,0)),"")

To many arguments would mean your Ifs went past the 3 criteria required (logical test, value_if_true, value_if_false). I removed the problematic quotes and didn't get any argument errors.

See if this works:


=IF(OR(A1="NEWARK",A1="GRAND RAPIDS",A1="MILWAUKEE",A1="WARREN"),"ACCESS RN",IF(OR(A1="BIRMINGHAM"),"ALABAMA PICC ",IF(OR(A1="VINTON",A1="GREENSBORO",A1="SIMPSON"),"CAROLINA WELNESS",IF(OR(A1="GREENSBURG",A1="HERMITAGE"),"CIAs",IF(OR(A1="VIRGINIA BEACH"),"INFUSION CARE SERVICES OF VA",IF(OR(A1="WARWICK "),"INFUSION SUPPORT SYSTEMS",IF(OR(A1="BELTSVILLE",A1="EASLEY",A1="LARGO",A1="LONGWOOD",A1="SARASOTA",A1="SMYRNA",A1="TALLAHASSEE"),"INSTITUTE OF NURSING EXCELLENCE",IF(OR(A1="CHARLESTON"),"INTRAVENOUS EXPERT CONSULTANTS",IF(OR(A1="FRIDLEY"),"ITIVA",IF(OR(A1="FT LAUDERDALE"),"IV ACCESS",IF(OR(A1="KENT"),"IV NURSE CONSULTANTS",IF(OR(A1="NEWARK",A1="KING OF PRUSSIA"),"IV QUEEN",IF(OR(A1="HARRISBURG",A1="WILKES BARRE"),"MAIN LINE IV",IF(OR(A1="DAPHNE ",A1="JACKSON",A1="MEMPHIS"),"MID SOUTH PICC ",IF(OR(A1="KNOXVILLE"),"NURSE PRO PLUS ",IF(OR(A1="SALT LAKE CITY",A1="BOISE"),"NURSING SERVICES, INC.",IF(OR(A1="WAILUKU ",A1="HILO",A1="HONOLULU",A1="KAUAI"),"P&A PICC, LLC ",IF(OR(A1="GRAND PRAIRIE "),"PICCs PLUS, LLC ",IF(OR(A1="HOUSTON "),"PICC VIC, INC ",IF(OR(A1="SAN ANTONIO "),"PICC VIC RN ",IF(OR(A1="SIOUX FALLS",A1="DES MOINES",A1="OMAHA"),"VASCULAR ACCESS PLUS",IF(OR(A1="SACRAMENTO",A1="UNION CITY",A1="VENTURA",A1="FRESNO",A1="RIVERSIDE",A1="CYPRESS",A1="EL PASO",A1="SAN DIEGO ",A1="SAN LUIS OBISPO ",A1="STOCKTON",A1="TUCSON",A1="ALBUQUERQUE",A1="BROOMFIELD",A1="COLORADO SPRINGS",A1="MOUNTAIN VIEW"),"VIC the PICC"))))))))))))))))))))))