Trying to do a triple lookup

[samdan]

Hi all,


I'm trying to do, essentially, a triple lookup. I've got 14 Matrices, and I want to select one of the matrices, a vertical value from the selected matrix, and a horizontal value from the selected matrix, and get the intersecting value. I've already got down the VLOOKUP/MATCH pair for the horizontal/vertical intersetion, but what I haven't gotten is how to create the dependent drop downs for the axis values based on the matrix picked. The matrices are big, like 20x20 or bigger, so I want them to each be on their own tab so that I can more easily edit the axes or values depending on future changes.


If it makes things easier, the table names for the horizontal/vertical values are essentially just "XVert" and "XHoriz" where X is the matrix in question. Can I create a dummy cell that is just (={matrix selection} & "Vert") and have the data validation pick the table name that's equal to that dummy cell's value?

 

[RasGhul]

If you can post some sample data showing where you want the result to be we could find a solution.

 

[samdan]

If you can post some sample data showing where you want the result to be we could find a solution.

Certainly! Here's a dummy file I was using to test out functions and dependent drop down uses. I have 3 simple matrices, each on their own sheet. I want the box in D3 to select the matrix necessary, then have D4 be a drop down of the vertical axis options for that matrix and D5 to be the horizontal axis options for that matrix. At that point, after selecting an option from D4 and D5, D7 will use a VLOOKUP(D4,array area<array>,MATCH(D5, top row extents<top row="" range="">),FALSE) to get me the intersection of the two options.

WeTransfer Link: https://we.tl/t-XXvyveRJOj</top></array>

 

[RasGhul]

Hi Samdan,

To keep the Matrices dynamic I recommend converting them to tables with CRTL+T.

For D4 Data Validation/List/Source;

=INDIRECT($D$3)

For D5 Data Validation/List/Source;

=INDIRECT($E$3)

Book1
	B	C	D	E	F	G	H	I	J	K
2								Matrices	Responses	Table Areas
3	Select Matrix:		mtrxC	Creact	Table3[#All]			mtrxA	Areact	Table1[#All]
4	Select Adjective:		Legolas					mtrxB	Breact	Table2[#All]
5	Select Response:		Merry					mtrxC	Creact	Table3[#All]
6
7	Paragraph Number:		43
Sheet1

Cell Formulas
Range	Formula
E3	=VLOOKUP($D$3,$I$3:$J$5,2)
F3	=VLOOKUP($D$3,$I$3:$K$5,3)
D7	=IFERROR(VLOOKUP($D$4,INDIRECT(F3),MATCH($D$5,INDIRECT($E$3),)+1,0),"")

https://www.dropbox.com/s/sm5g7ztcck4joo6/Triple_jp.xlsx?dl=0

 

[RasGhul]

Samdan,

If this ends up being too slow with vlookups we may need to swap over to a index & match version.

 

[samdan]

Samdan,

If this ends up being too slow with vlookups we may need to swap over to a index & match version.

Thanks, I'll give this a try tomorrow and let you know if I have any issues! This is just to make it way easier to play a game I like, so I'm sure it will be plenty fast

 

[samdan]

It works with no issues at all, thank you so much for the help!

 

[RasGhul]

You're welcome