Return Value Based On Multiple Drop Down Lists

BrandynBlaze

New Member
Joined
Sep 20, 2012
Messages
29
Hello,

I'm having a difficult time figuring out the best way to approach a problem and I was hoping someone could help.

I'm trying to return a value based on two drop down lists. I've created 3 tables that contain the data that I want returned depending on the first drop down by creating the named references "Formulations", "Raw Materials", and "Distillations".

I then created a 4th table that is just a list of those tables. In order to create my drop-down menu I used data validation to list the 3 values in that 4th column. Using the "=Indirect()" formula in the data validation I was able to get my second list to populate with the values found in each of the 3 tables I wanted to reference depending on what the first drop down value is. What I have been unable to do so far is return the value in the cell directly to the right of the cell the second drop down references, which would be the corresponding density. I have a feeling that I need to use Vlookup but I'm not sure how I could reference the different tables based on the drop down selections. It seems that if I could just reference the cell location that was populating my second drop down list and add 1 to the column it would be the easiest way, but I'm not sure how I would do that without VBA. I'm avoiding that solution because the spreadsheet will be used by people with very little Excel experience and I want it to function as close to a normal spreadsheet as possible, with no macro or privacy warnings.

Hopefully that is enough info, I tried to post some pictures to help explain the process but I'm having trouble finding a way to host them so I can use a URL.

Brandyn


Tables.PNG
Tables.PNG
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
I tried to duplicate your sheet based on your description, and came up with this:

ABCDEFGHIJKLMN
TypeValueAssociated ValueFormulationsRaw_MaterialsDistillationsMy_Names
Raw_MaterialsGGADDHHHFormulations
BEEIIIRaw_Materials
CFFDistillations
GG

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]44[/TD]

[TD="align: right"]888[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]55[/TD]

[TD="align: right"]999[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet21

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=INDEX($G$2:$K$10,MATCH(B2,INDEX($F$2:$J$10,0,MATCH(A2,$F$1:$J$1,0)),0),MATCH(A2,$F$1:$J$1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Columns F, H, J, and M are named ranges, starting in row 2 and with the name in row 1.

A2 has a Data Validation rule: =My_Names

B2 has a Data Validation rule: =INDIRECT(A2)

and C2 has the INDEX/MATCH/MATCH/MATCH formula shown. It requires that the first 3 named ranges all be adjacent. If they aren't, it'll be much tougher, but let us know, it still could be possible.



EDIT: This formula looks to be a bit simpler, and doesn't require the named ranges to be adjacent:

=OFFSET(INDEX(INDIRECT(A2),MATCH(B2,INDIRECT(A2),0)),0,1)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top