IF formula , with item found on another sheet

malky neiman

Hi,

I am trying to create a formula. i want to return a patient's status based on if that patient is found on another sheet.

I have 2 sheets

1. list of active patients
2. available cases 7.6

I have a an admission ID on both sheets which can be used in a vlookup type of formula.

if the admission ID is found on the "available cases 7.6" sheet, it should return a status of "Case is open" on the list of active patients sheet in the column of "Available cases". if the admission ID is not found on the available cases 7.6 sheet, it should return a status of "Filled" on the list of active patients sheet in the column of "Available cases".

im attaching screenshots of both sheets

thank you for ur help

Lucas Rayala

Hi @malky neiman, you can use a countifs to look for the number of occurrences of a your ID. If the count is greater than zero, you know it's on the page.

=IF(COUNTIFS({Admission ID}, [Admission ID]@row)>0, "Case is open", "Filled")

Lucas Rayala

Hi @malky neiman, you can use a countifs to look for the number of occurrences of a your ID. If the count is greater than zero, you know it's on the page.

=IF(COUNTIFS({Admission ID}, [Admission ID]@row)>0, "Case is open", "Filled")

malky neiman

This worked! thank you so much for your help