How to perform "IF(ISNA(VLOOKUP.." or "IF(ISERROR(MATCH.." when there are multiple items in cell

Zach C

The sheets I have:

1. Hospital Notification Survey Form - where Regional Directors attest that they have notified one or more hospitals of the upcoming change (for simplicity, I'll say there's 10 total hospitals)
2. Hospital Notification Tracking Sheet - will track submissions from the above survey form's sheet, which ultimately rolls up to a third sheet that summarizes all regions/division total

What I need:

• To be able to perform a "IF(ISNA(VLOOKUP.." or "IF(ISERROR(MATCH.." function to return a "Yes" or "No" when Regional Directors submit new survey form for respective Hospital Names

Where I'm currently stumped:

• When a Regional Director selects Hospitals 1 through 5, I'm unable to return a "Yes" or "No" for a IF(ISERROR(MATCH function, or a "1" for a COUTNIFS(HAS function in order to automatically tell me and my team if this is done.
• Currently just getting "No Match" or "Invalid Column Formula" from what I've tried

What my tracking sheet looks like

What submissions look like (for this scenario I've used my name and generic Hospitals 1-5)

What my survey looks like on the back-end (top 2 items are hidden ie auto-populate to sheet)

KPH

Hi @Zach C

If your submission sheet contains a multi-select drop down of hospital names, like this:

Then in your tracker sheet you can count the number of times the hospital name appears in the notified column (using a COUNTIF function with a cross sheet reference).

If this number is more than 0, you can check the box or return "yes" or "no" in the Hospital Notified column (using an IF function).

Like this:

Or this:

In both examples:

{Submission Region 1 Notified}

refers to the "Region 1 hospitals you have notified" column in the other sheet.

Let me know how you get on.

Paul Newcome

@Zach C It looks like your first screenshot has "Hospital 1" but your second screenshot has "Hospital Name 1". Make sure the text you are searching for is actually a match for a selection made in the source sheet.

KPH

Hi @Zach C

If your submission sheet contains a multi-select drop down of hospital names, like this:

Then in your tracker sheet you can count the number of times the hospital name appears in the notified column (using a COUNTIF function with a cross sheet reference).

If this number is more than 0, you can check the box or return "yes" or "no" in the Hospital Notified column (using an IF function).

Like this:

Or this:

In both examples:

{Submission Region 1 Notified}

refers to the "Region 1 hospitals you have notified" column in the other sheet.

Let me know how you get on.

Zach C

Hey @KPH thank you for your reply! I tried the second formula you shared and it's giving me a "No". What are your thoughts/suggestions?

Formula used below

Hospital 1 showing in source sheet

KPH

You should have just one cross sheet reference in this formula and it should be created on the "Region 1 Hospitals You Have Notified" column.

In your formula, that cross sheet reference is used for the range for the COUNTIF. The range for the HAS is @cell not another cross sheet reference.

Zach C

@KPH I'm just referencing the one column in the survey sheet below. I'm still getting "No" for the formula.

Paul Newcome

@Zach C It looks like your first screenshot has "Hospital 1" but your second screenshot has "Hospital Name 1". Make sure the text you are searching for is actually a match for a selection made in the source sheet.

Zach C

@Paul Newcome good shout, thanks for flagging! I totally overlooked that and after making that adjustment, the formula is working now. Thank you! And thank you @KPH for the solution (was just an error on my part)!

Zach C

@Paul Newcome/@KPH another question related to this, how would you go about resolving the below (If a survey is net new and there aren't responses yet)?

KPH

Can you add a dummy response and then delete it?

Paul Newcome

I put a placeholder row into the reference sheet. If this is for a larger solution, I make sure that however I have my placeholder row set up, it doesn't get included in any metrics or reports even if that means creating a filter that specifically excludes the word "Placeholder".

Zach C

@KPH/@Paul Newcome both of these work - thanks! I'll see what makes the most sense as I scale this to multiple geographies.