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 don't believe two different arrays can be used for a reference in an Index.

You could try these two in their own columns, then merge them:

Column A:
=IFERROR(INDEX(Sheet1!$C$27:$C$545,MATCH(E2,Sheet1!$B$27:$B$545,0)),"")

Or

If need E2 compared to B27:B545 & A2 compared to D3:D:32 use:
{=IFERROR(INDEX(Sheet1!$C$27:$C$545,MATCH(E2&A2,Sheet1!$B$27:$B$545&Sheet2!$D$3:$D$32,0)),"")}
- To get { }, use Ctrl + Shift + Enter, don't manually add them.

Column B:
=IFERROR(INDEX(Sheet2!E3:E32,MATCH(A2,Sheet2!D3:D32,0)),"")

Or

{=IFERROR(INDEX(Sheet2!E3:E32,MATCH(E2&A2,Sheet1!$B$27:$B$545&Sheet2!$D$3:$D$32,0)),"")}



Column C [aka Results] (taking whatever the result of Column A first, if Column A is blank, take Column's B result):
=if(a1="",b1,a1)

 

[basicallychels]

THANKS, THAT WORKED!ray: