Problem $3$: Esperanza House is a not$-$for$-$profit organization that provides comfortable, affordable, temporary lodging for patients and their families who must travel to Chicago for medical treatment. Worksheet Problem $3$ contains information on a subset of patients they have supported.

a$.$ Use a data validation rule to create a list that shows all patient IDs listed in the database in cell

B$4.$

Based on the patient ID selected in B$4,$ use VLOOKUP or HLOOKUP and MATCH to determine the patient$$s Doctor of record and Charged Amount $($$$)$ associated with serving the patient. Place your answers in cells E$4$ and E$5,$ respectively. You should enter one formula in E$4$ that can be copied down to E$5.$

Find the total billed $($across all patient IDs$)$ for the Doctor listed in E$4.$ Place answer in cell E$6.$

Based on the patient ID selected in B$4,$ use INDEX and MATCH to determine the Doctor and Charged Amount $($$$)$ associated with serving the patient. Place your answers in cells F$4$ and F$5,$ respectively. You should enter one formula in F$4$ that can be copied down to F$5.$

Note: The formulas you place in row $4$ should be appropriately referenced so that they can be copied to row $5$ without any changes.

Based on the patient ID selected in B$4,$ use OFFSET and MATCH to determine the Doctor and Charged Amount $($$$)$ associated with serving the patient. Place your answers in cells G$4$ and G$5,$ respectively. Do not hardcode values.

Create a Conditional Formatting rule that highlights any row where the amount unpaid exceeds $$500.$

g$.$ Create a Conditional formatting rule that highlights all instances in the data that match the Doctor$$s in cell E$4,$ and the corresponding Charged Amount$($$$)$ in all instances. Problem $3$: Esperanza House is a not$-$for$-$profit organization that provides comfortable, affordable, temporary lodging for patients and their families who must travel to Chicago for medical treatment. Worksheet Problem $3$ contains information on a subset of patients they have supported.

a$.$ Use a data validation rule to create a list that shows all patient IDs listed in the database in cell B$4.$

b$.$ Based on the patient ID selected in B$4,$ use VLOOKUP or HLOOKUP and MATCH to determine the patient's Doctor of record and Charged Amount $(\backslash$$$)$ associated with serving the patient. Place your answers in cells E$4$ and E$5,$ respectively. You should enter one formula in E$4$ that can be copied down to E$5.$

c$.$ Find the total billed $($across all patient IDs$)$ for the Doctor listed in E$4.$ Place answer in cell E$6.$

d$.$ Based on the patient ID selected in B$4,$ use INDEX and MATCH to determine the Doctor and Charged Amount $(\backslash$$$)$ associated with serving the patient. Place your answers in cells F$4$ and F$5,$ respectively. You should enter one formula in F$4$ that can be copied down to F$5.$

Note: The formulas you place in row $4$ should be appropriately referenced so that they can be copied to row $5$ without any changes.

e$.$ Based on the patient ID selected in B$4,$ use OFFSET and MATCH to determine the Doctor and Charged Amount $(\backslash$$$)$ associated with serving the patient. Place your answers in cells G$4$ and G$5,$ respectively. Do not hardcode values.

f$.$ Create a Conditional Formatting rule that highlights any row where the amount unpaid exceeds $\backslash$$$500.$