Return Value Based On Multiple Drop Down Lists

[BrandynBlaze]

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

 

[Eric W]

I tried to duplicate your sheet based on your description, and came up with this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Type	Value	Associated Value			Formulations		Raw_Materials		Distillations			My_Names
2	Raw_Materials	GG	77			A	1	DD	44	HHH	888		Formulations
3						B	2	EE	55	III	999		Raw_Materials
4						C	3	FF	66				Distillations
5								GG	77

<tbody>
</tbody>

Sheet21​

Worksheet Formulas Cell Formula C2 =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)) <tbody> </tbody>

<tbody>
</tbody>

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)