Index and Match on multiple similar tables

[infinia]

Hello,

I need to use Index and Match function for this problem.
Need to select the specific table using the first parameter and then a value inside the table by using two other parameters.

eg

Need to find Shipping per unit using the tables below

 

[infinia]

*Need to select the specific table using the "customer type" parameter and then a value inside that table by using "region" and "shipping method"

 

[sandy666]

picture is useless, post copyable example data using xl2bb

 

[keithmct]

welcome to the board.
I've had this same problem fixed for me as follows:
It's easier to understand if you name some ranges.
Name B37:B41 as customer10region
Name C36:F36 as customer10method
Name C37:F41 as customer10rate

do the same for the other 2 customer types substituting the number as required (customer20region etc)

I made your input options as lists in data validation so they are pull down menus, but I made customer type 10, customer10.
I copied your input table starting at A24 and the shipping rate result is in A31

then the formula under "shipping per unit" is:
=INDEX(INDIRECT(A29&"rate"),MATCH(A25,INDIRECT(A29&"region"),0),MATCH(A27,INDIRECT(A29&"method"),0))

I tested it and it works. Hope for the same for you.

 

[keithmct]

Here it is using xl2BB

shipping rate with index match.xlsx
	A	B	C	D	E	F	G
23
24	region
25	WEST
26	shipping method
27	PLANE
28	customer type
29	customer20
30	shipping per unit
31	$ 1.83
32
33
34		customer type 10
35			std
36		region	rail	truck	plane	ship
37		NW	$0.13	$0.14	$1.53	$0.23
38		WEST	$0.21	$0.15	$2.03	$0.25
39		SW	$0.27	$0.17	$2.13	$0.23
40		MIDWEST	$0.31	$0.19	$2.28	$0.23
41		EAST	$0.48	$0.43	$3.53	$0.38
42
43		customer type 20
44			preferred
45		region	rail	truck	plane	ship
46		NW	$0.12	$0.13	$1.38	$0.21
47		WEST	$0.19	$0.14	$1.83	$0.22
48		SW	$0.25	$0.16	$1.92	$0.21
49		MIDWEST	$0.28	$0.17	$2.06	$0.21
50		EAST	$0.44	$0.39	$3.18	$0.35
51
52		customer type 30
53			most preferred
54		region	rail	truck	plane	ship
55		NW	$0.11	$0.12	$1.25	$0.18
56		WEST	$0.18	$0.13	$1.65	$0.17
57		SW	$0.22	$0.14	$1.73	$0.19
58		MIDWEST	$0.26	$0.16	$1.85	$0.19
59		EAST	$0.39	$0.35	$2.87	$0.31
Sheet1

Cell Formulas
Range	Formula
A31	A31	=INDEX(INDIRECT(A29&"rate"),MATCH(A25,INDIRECT(A29&"region"),0),MATCH(A27,INDIRECT(A29&"method"),0))

Cells with Data Validation
Cell	Allow	Criteria
A25	List	NW,WEST,SW,MIDWEST,EAST
A27	List	RAIL,TRUCK,PLANE,SHIP
A29	List	customer10,customer20,customer30

 

[sandy666]

using xl2bb from post#5 here is Power Query (Get&Transform) solution

s666-PQ-Index and Match on multiple similar tables.xlsx

Microsoft Excel Workbook

1drv.ms

 

[Peter_SSs]

Welcome to the Mr Excel board!

Since your tables all appear to have an identical layout & row/column headings, you can use standard INDEX/MATCH functions without any named ranges or use of the volatile INDIRECT function.

20 08 06.xlsm
	A	B	C	D	E	F	G
24	region
25	WEST
26	shipping method
27	PLANE
28	customer type
29	customer type 30
30	shipping per unit
31	1.65
32
33
34		customer type 10
35			std
36		region	rail	truck	plane	ship
37		NW	0.13	0.14	1.53	0.23
38		WEST	0.21	0.15	2.03	0.25
39		SW	0.27	0.17	2.13	0.23
40		MIDWEST	0.31	0.19	2.28	0.23
41		EAST	0.48	0.43	3.53	0.38
42
43		customer type 20
44			preferred
45		region	rail	truck	plane	ship
46		NW	0.12	0.13	1.38	0.21
47		WEST	0.19	0.14	1.83	0.22
48		SW	0.25	0.16	1.92	0.21
49		MIDWEST	0.28	0.17	2.06	0.21
50		EAST	0.44	0.39	3.18	0.35
51
52		customer type 30
53			most preferred
54		region	rail	truck	plane	ship
55		NW	0.11	0.12	1.25	0.18
56		WEST	0.18	0.13	1.65	0.17
57		SW	0.22	0.14	1.73	0.19
58		MIDWEST	0.26	0.16	1.85	0.19
59		EAST	0.39	0.35	2.87	0.31
60
Lookup multiple tables

Cell Formulas
Range	Formula
A31	A31	=INDEX(C34:F59,MATCH(A29,B34:B59,0)+MATCH(A25,B37:B41,0)+2,MATCH(A27,C36:F36,0))

 

[keithmct]

Since your tables all appear to have an identical layout & row/column headings, you can use standard INDEX/MATCH functions without any named ranges or use of the volatile INDIRECT function.

Peter, just curious, why does my formula need the "volatile indirect function"? I don't profess to understand how all the magic works, I copy and paste a lot. Is it because my tables are all different sizes and headings?

 

[Peter_SSs]

Peter, just curious, why does my formula need the "volatile indirect function"?

Because the solution used Named Ranges for parts of each table and then the formula used the text in cell A29 as part of the range name. It just wouldn't work without INDIRECT