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

Copper Contributor

Greetings,

 

Here is what I am trying to create:

Sheet # 1 - has a total of 4 cells that will have data in them. First two cells will be drop downs which pull data from Sheet # 2. The 3rd and 4th cells will produce results based on data from Sheet # 2 based on that selected in drop down of the first two cells.

 

Example:

Sheet # 1, Cell # 1 - Named starting hospital, pulls drop down data from column A in Sheet # 2

 

Sheet # 1, Cell # 2 - Named ending hospital, pulls drop down data from row 1 in Sheet # 2

 

Sheet # 1, Cell # 3 - produces a result of the selection of Cell # 1 and Cell # 2 that references Sheet # 2 where column A and row 1 would intersection on Sheet # 2 holding that data. This data will be a numerical value as the data result.

 

Sheet # 1, Cell # 4 - produces a fixed dollar amount based on the value range of the number in Sheet # 1, Cell # 3.

 

Sheet # 2 is a pattern so if I look at column A and look at the third hospital down (row # 3) and then look along the top at the fifth hospital in (column F) the value of F3 would be a numerical value. 

 

I hope this makes sense as to what I am trying to create in this workbook.

1 Reply

@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.