Reference drop down values from another sheet and lookup data from 2nd sheet

John_Kruk 

You can achieve this by using named ranges, data validation for dropdowns, and the INDEX and MATCH functions for looking up values based on the selections. Here is a step-by-step guide:

Sheet #2 - Set up the Data

Assuming your data in Sheet #2 looks something like this:

markdown

          | A          | B           | C          | D

---------------------------------------------------------

1         | (Blank)    | Hospital 1  | Hospital 2 | Hospital 3

---------------------------------------------------------

Hospital 1| (Blank)    | 10          | 15         | 20

Hospital 2| (Blank)    | 25          | 30         | 35

Hospital 3| (Blank)    | 40          | 45         | 50

Sheet #1 - Set up Dropdowns and Formulas

1.Set up Named Ranges:

• Go to Formulas -> Name Manager.
• Create two named ranges:
• Name: Hospitals, Refers to: Sheet2!$A$2:$A$4
• Name: Values, Refers to: Sheet2!$B$2:$D$4

2. Set up Data Validation for Dropdowns:

• Select Cell #1 (Starting Hospital).
• Go to Data -> Data Validation.
• Allow: List, Source: =Hospitals

Repeat the same process for Cell #2 (Ending Hospital).

3. Calculate the Result in Cell #3:

• In Cell #3, use the following formula:

=INDEX(Values, MATCH([@StartingHospital], Hospitals, 0), MATCH([@EndingHospital], Hospitals, 0))

This formula uses INDEX and MATCH functions to find the intersection of the selected hospitals in the data range.

4.  Calculate Fixed Dollar Amount in Cell #4:

• In Cell #4, use a formula based on the value in Cell #3. For example:

=IF(AND(C3>=10, C3<=20), 50, IF(AND(C3>=25, C3<=35), 75, IF(AND(C3>=40, C3<=50), 100, 0)))

• Adjust the value ranges and corresponding fixed dollar amounts according to your specific requirements.

Now, when you select hospitals in Cells #1 and #2, Cells #3 and #4 should dynamically update based on the data from Sheet #2. Adjust the formulas as needed based on the actual layout and data in your sheets. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.