Using two data validation lists to select element in array

[deucekhk]

I am trying to use two seperate dropdown lists to select a specific value in a table of set values. The first drop down will select the row and the second will select one of two columns. I have the data validation lists ready to go but it looks like the main hangup I'm getting is having the VLOOKUP use the selected value from the dropdowns in the formula. Any help would be appreciated.

 

[C Moore]

Sounds as if you should be using the index function instead of vlookup.

 

[deucekhk]

Sounds as if you should be using the index function instead of vlookup.

Here is what I tried for index but am still getting stuck:

=INDEX(Formulas!A8:C22[this is the whole table],ROW(HVL!B12)[this is my first dropdown box],COLUMN(HVL!H12)[this is the second dropdown box])

I'm getting a reference error. I believe the ROW/COLUMN(Dropdown cell) isn't returning a number to reference which row/column.

 

[deucekhk]

Finally got it! Here is what I did in case anyone has similar problems. I made two separate tables next to the main one. First table was all the choices for my first drop down box with one column assigning number to each selection and I put a cell with a vlookup formula with the following conditions(drop box1, new table,column of numbers,false) Second table was the second drop box done the same way with a cell holding another vlookup for assigning number to the drop box selection. then in the main page i used an index formula using the two vlookup cells as row and column number. easy peasy

 

[Peter_SSs]

Sounds like you have a working solution, but you should be able to do this with a single, direct formula without additional tables.

I'm not sure which sheet your final result is on ("main page"?) but see if you can make something from this.

Excel Workbook
	A	B	C	D
8	H1	H2	H3
9	L1	a	o
10	L2	b	p
11	L3	c	q
12	L4	d	r
13	L5	e	s
14	L6	f	t
15	L7	g	u
16	L8	h	v
17	L9	i	w
18	L10	j	x
19	L11	k	y
20	L12	l	z
21	L13	m	aa
22	L14	n	ab
23
Formulas

Excel Workbook
	A	B	C	D	E	F	G	H	I
11		Row						Col	Result
12		L9						H3	w
HVL