Am I able to lookup a value in a table? 

For instance, the current formula looks like =If(PPM Sponsoring Division IN ("Hospital 1","Hospital 1", "Hospital 1"),"Y","N")

I want to create a table so there isn't the need to manually update each formula with new hospitals so I would hopefully like it to be something like 

=If(PPM Sponsoring Division IN (New table), "Y","N")

Not sure if I am completely understanding your question but yes, you can use a lookup function:

= Lookup ( [source column] , [lookup table], [matching column], [lookup value column], ...*)

 So, you would create a lookup table that would have the list of your hospitals (that you would update when needed) with the return values you needed.

Let me know if I misunderstood your question though.

Thanks for the reply! It's pretty close. So the lookup table just has the names of the hospitals and nothing else, so there isn't a source/matching column. 

I didn't know if there was a way to do it without the source/matching columns.

Perhaps the Tablematch function is what you seek.  Here is an article that explains it:  TableMatch function .

Hi @Nicholas Marchese, it sounds like you want to check if the values in your "PPM Sponsoring Division" column exist in a reference table/list - moving from a formula with hard-coded values (i.e. IN ("Hospital 1", "Hospital 2", "Hospital 3") ) to a more dynamic one that checks another table.

If so, then you can go with either what Gordana (Lookup) or Angela (Tablematch) is recommending.

The Lookup approach might be the simpler solution because it is possible to return the Matching Column itself.

For example, if you have a table that contains a list of "PPM Sponsoring Division" w/o any return values in mind, then you can use a combination of the "If" and "Lookup" formulas like the example below:

Example:

=If(Lookup(PPM Sponsoring Division, [PPM Sponsoring Division Table Name], [PPM Sponsoring Division Column Name], [PPM Sponsoring Division Column Name])!="","Y","N")

The logical test here (!="") checks if the lookup formula is returning a non-null/not blank value.

Since Lookups can return the Matching Column itself, wherever there's a match it would evaluate as not blank and return "Y"; otherwise, it would return "N".