Index Match With Tables Not Working

[DeaninGA]

All,

I have an issue with Index and match In tables Returning an N/A.
Heres the Formula =INDEX(Table13[#All],MATCH([@[Frame Only]],Table13[Window Size],0),MATCH([@[SDL Light]],Table13[#All],0))

Table 13 is where my price table is I want to pull it in based on 2 values Frame Only and SDL Light ( both Named fields on my main table.)

Thank you in advance
Dean

 

[Fluff]

Is the SDL Light looking for a column header?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[DeaninGA]

Is the SDL Light looking for a column header?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’

Yes It is here's a screen shot of table 13. @Fluff I will fix my account details momentarily

 

[Fluff]

In that case try

=INDEX(Table13,MATCH([@[Frame Only]],Table13[Window Size],0),MATCH([@[SDL Light]],Table13[#Headers],0))

 

[DeaninGA]

In that case try

=INDEX(Table13,MATCH([@[Frame Only]],Table13[Window Size],0),MATCH([@[SDL Light]],Table13[#Headers],0))

Hmm Something is still wrong, Both My Row and Column s in table 13 are exact as Im using a Data Validation in my Main table to keep everything exact so we pull the right information. Heres the Main table Im pulling That table 13 information into.. I used your formula above and still getting NA. Thanks again @Fluff

 

[Fluff]

It's probably the * which is being treated as a wildcard. Change both Match functions to XMATCH

 

[DeaninGA]

It's probably the * which is being treated as a wildcard. Change both Match functions to XMATCH

I changed Both and still coming up N/A... any other ideas... im baffled.I really do appreciate the help



=INDEX(Table13,XMATCH([@[Frame Only]],Table13[Window Size],0),XMATCH([@[SDL Light]],Table13[#Headers],0))

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[DeaninGA]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Here it is @Fluff Both My Main table ( where Im pulling the data to) and Table 13 my cost table.

Test Pricing tool.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1									Order Total										#N/A
2
3	Line	Frame Only	Glass Type	Low E	Jamb	Exterior Trim	Casing	SDL Light	Transoms	Window Base Price	Glass Add on Price	Low E Cost Add on	Jamb Price add on	Trim Pricing Add On	Casing Price Add On	SDL Add On	Transom Price Add on	Sum Each	QTY	Line Total
4	1	2/0*3/2- Double Hung	Clear	Yes	4-9/16	5/4*4	544F	8	Transom 1	$10.00	$12.00	$10.00	$0.90	$1.75	$0.98	#N/A	$0.00	#N/A	1	#N/A
5	2	2/0*5/2- Double Hung	Clear	Yes	4-9/16	5/4*4	546PR	2	Transom 1	$13.00	$15.00	$16.00	$0.96	$1.78	$1.10	#N/A	$0.00	#N/A	2	#N/A
6	3	2/0*3/2- Double Hung	GBG	Yes	6-9/16	1*4	546F	8	Transom 3	$10.00	$15.00	$10.00	$1.25	$1.50	$0.98	#N/A	$0.00	#N/A	4	#N/A
7	4	3/0*6/2- Double Hung	GBG	No	4-9/16	1*4	P14281	6	Transom 2	$29.00	$24.50	0	$1.28	$1.69	$1.74	#N/A	$0.00	#N/A	2	#N/A
8	5											0				#N/A	$0.00	#N/A		#N/A
9	6											0				#N/A	$0.00	#N/A		#N/A
10	7											0				#N/A	$0.00	#N/A		#N/A
11	8											0				#N/A	$0.00	#N/A		#N/A
12	9											0				#N/A	$0.00	#N/A		#N/A
13	10											0				#N/A	$0.00	#N/A		#N/A
14	11											0				#N/A	$0.00	#N/A		#N/A
15	12											0				#N/A	$0.00	#N/A		#N/A
16	13											0				#N/A	$0.00	#N/A		#N/A
17	14											0				#N/A	$0.00	#N/A		#N/A
18	15											0				#N/A	$0.00	#N/A		#N/A
19	16											0				#N/A	$0.00	#N/A		#N/A
20	17											0				#N/A	$0.00	#N/A		#N/A
21	18											0				#N/A	$0.00	#N/A		#N/A
22	19											0				#N/A	$0.00	#N/A		#N/A
23	20											0				#N/A	$0.00	#N/A		#N/A
24	21											0				#N/A	$0.00	#N/A		#N/A
25	22											0				#N/A	$0.00	#N/A		#N/A
26	23											0				#N/A	$0.00	#N/A		#N/A
27	24											0				#N/A	$0.00	#N/A		#N/A
28	25											0				#N/A	$0.00	#N/A		#N/A
Tool

Cell Formulas
Range	Formula
S1	S1	= SUM(Table7[Line Total])
J4:J28	J4	=IFNA(XLOOKUP([@[Frame Only]],Table6[[#All],[Window Size]],Table6[[#All],[Base Price]],,0,1),"")
K4:K28	K4	=IFERROR(VLOOKUP([@[Frame Only]],Table6[#All],IF([@[Glass Type]]="Tempered",4,IF([@[Glass Type]]="Clear",3,IF([@[Glass Type]]="SDL",4,IF([@[Glass Type]]="GBG",5,""))))),"")
L4:L28	L4	=IF([@[Low E]]="Yes",VLOOKUP([@[Frame Only]],Table6[#All],7,FALSE),"0")
M4:M28	M4	=IFERROR(VLOOKUP([@[Frame Only]],Table6[#All],IF([@[Jamb ]]="4-9/16",9,IF([@[Jamb ]]="6-9/16",10,""))),"")
N4:N28	N4	=IFERROR(VLOOKUP([@[Frame Only]],Table6[#All],IF([@[Exterior Trim]]="Brick Mold",10,IF([@[Exterior Trim]]="1*4",11,IF([@[Exterior Trim]]="5/4*4",12,"")))),"")
O4:O28	O4	=IFNA(VLOOKUP([@[Frame Only]],Table6[#All],13,FALSE),"")
P4:P28	P4	=INDEX(Table13,XMATCH([@[Frame Only]],Table13[Window Size],0),XMATCH([@[SDL Light]],Table13[#Headers],0))
Q4:Q28	Q4	=IFERROR(VLOOKUP([@Transoms],#REF!,2,FALSE),0)
R4:R28	R4	=SUM(Table7[@[Window Base Price]:[Transom Price Add on]])
T4:T28	T4	=[@[Sum Each]]*[@QTY]

Cells with Data Validation
Cell	Allow	Criteria
B4:B28	List	='Data Validation Lists'!$A$3:$A$37
C4:C28	List	='Data Validation Lists'!$C$3:$C$6
D4:D28	List	='Data Validation Lists'!$M$3:$M$4
E4:E28	List	='Data Validation Lists'!$E$3:$E$4
F4:F28	List	='Data Validation Lists'!$G$3:$G$5
G4:G28	List	='Data Validation Lists'!$O$3:$O$14
H4	List	='Data Validation Lists'!$I$3:$I$15
I4:I29	List	='Data Validation Lists'!$K$3:$K$6
H5:H28	List	='Data Validation Lists'!$I$4:$I$15

 

[DeaninGA]

Test Pricing tool.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
39	Window Size	0	2	3	4	6	8	9	12	16	18	24	36	48	Transom 1	Transom 2	Transom 3
40	2/0*3/2- Double Hung	$1.00	$1.00	$1.00	$1.00	$1.00	$1.00	$1.00	$1.00	$1.00	$1.00	$1.00	$1.00	$1.00
41	2/0*3/10- Double Hung	$1.05	$1.06	$1.07	$1.08	$1.09	$1.10	$1.11	$1.12	$1.13	$1.14	$1.15	$1.16	$1.17
42	2/0*4/6- Double Hung	$1.10	$1.09	$1.08	$1.07	$1.06	$1.05	$1.04	$1.03	$1.02	$1.01	$1.00	$0.99	$0.98
43	2/0*5/2- Double Hung	$1.15	$1.12	$1.09	$1.06	$1.03	$1.00	$0.97	$0.94	$0.91	$0.88	$0.85	$0.82	$0.79
44	2/0*6/2- Double Hung	$1.20	$1.15	$1.10	$1.05	$1.00	$0.95	$0.90	$0.85	$0.80	$0.75	$0.70	$0.65	$0.60
45	2/4*3/2- Double Hung	$1.25	$1.18	$1.11	$1.04	$0.97	$0.90	$0.83	$0.76	$0.69	$0.62	$0.55	$0.48	$0.41
46	2/4*3/10- Double Hung	$1.30	$1.21	$1.12	$1.03	$0.94	$0.85	$0.76	$0.67	$0.58	$0.49	$0.40	$0.31	$0.22
47	2/4*4/6- Double Hung	$1.35	$1.24	$1.13	$1.02	$0.91	$0.80	$0.69	$0.58	$0.47	$0.36	$0.25	$0.14	$0.03
48	2/4*5/2- Double Hung	$1.40	$1.27	$1.14	$1.01	$0.88	$0.75	$0.62	$0.49	$0.36	$0.23	$0.10	-$0.03	-$0.16
49	2/4*6/2- Double Hung	$1.45	$1.30	$1.15	$1.00	$0.85	$0.70	$0.55	$0.40	$0.25	$0.10	-$0.05	-$0.20	-$0.35
50	2/8*3/2- Double Hung	$1.50	$1.33	$1.16	$0.99	$0.82	$0.65	$0.48	$0.31	$0.14	-$0.03	-$0.20	-$0.37	-$0.54
51	2/8*3/10- Double Hung	$1.55	$1.36	$1.17	$0.98	$0.79	$0.60	$0.41	$0.22	$0.03	-$0.16	-$0.35	-$0.54	-$0.73
52	2/8*4/6- Double Hung	$1.60	$1.39	$1.18	$0.97	$0.76	$0.55	$0.34	$0.13	-$0.08	-$0.29	-$0.50	-$0.71	-$0.92
53	2/8*5/2- Double Hung	$1.65	$1.42	$1.19	$0.96	$0.73	$0.50	$0.27	$0.04	-$0.19	-$0.42	-$0.65	-$0.88	-$1.11
54	2/8*6/2- Double Hung	$1.70	$1.45	$1.20	$0.95	$0.70	$0.45	$0.20	-$0.05	-$0.30	-$0.55	-$0.80	-$1.05	-$1.30
55	3/0*3/2- Double Hung	$1.75	$1.48	$1.21	$0.94	$0.67	$0.40	$0.13	-$0.14	-$0.41	-$0.68	-$0.95	-$1.22	-$1.49
56	3/0*3/10- Double Hung	$1.80	$1.51	$1.22	$0.93	$0.64	$0.35	$0.06	-$0.23	-$0.52	-$0.81	-$1.10	-$1.39	-$1.68
57	3/0*4/6- Double Hung	$1.85	$1.54	$1.23	$0.92	$0.61	$0.30	-$0.01	-$0.32	-$0.63	-$0.94	-$1.25	-$1.56	-$1.87
58	3/0*5/2- Double Hung	$1.90	$1.57	$1.24	$0.91	$0.58	$0.25	-$0.08	-$0.41	-$0.74	-$1.07	-$1.40	-$1.73	-$2.06
59	3/0*6/2- Double Hung	$1.95	$1.60	$1.25	$0.90	$0.55	$0.20	-$0.15	-$0.50	-$0.85	-$1.20	-$1.55	-$1.90	-$2.25
60	4/0*3/2 - Fixed Picture Window	$2.00	$1.63	$1.26	$0.89	$0.52	$0.15	-$0.22	-$0.59	-$0.96	-$1.33	-$1.70	-$2.07	-$2.44
61	4/0*3/10 - Fixed Picture Window	$2.05	$1.66	$1.27	$0.88	$0.49	$0.10	-$0.29	-$0.68	-$1.07	-$1.46	-$1.85	-$2.24	-$2.63
62	4/0*4/6 - Fixed Picture Window	$2.10	$1.69	$1.28	$0.87	$0.46	$0.05	-$0.36	-$0.77	-$1.18	-$1.59	-$2.00	-$2.41	-$2.82
63	4/0*5/2 - Fixed Picture Window	$2.15	$1.72	$1.29	$0.86	$0.43	$0.00	-$0.43	-$0.86	-$1.29	-$1.72	-$2.15	-$2.58	-$3.01
64	4/0*6/2 - Fixed Picture Window	$2.20	$1.75	$1.30	$0.85	$0.40	-$0.05	-$0.50	-$0.95	-$1.40	-$1.85	-$2.30	-$2.75	-$3.20
65	5/0*3/2 - Fixed Picture Window	$2.25	$1.78	$1.31	$0.84	$0.37	-$0.10	-$0.57	-$1.04	-$1.51	-$1.98	-$2.45	-$2.92	-$3.39
66	5/0*3/10 - Fixed Picture Window	$2.30	$1.81	$1.32	$0.83	$0.34	-$0.15	-$0.64	-$1.13	-$1.62	-$2.11	-$2.60	-$3.09	-$3.58
67	5/0*4/6 - Fixed Picture Window	$2.35	$1.84	$1.33	$0.82	$0.31	-$0.20	-$0.71	-$1.22	-$1.73	-$2.24	-$2.75	-$3.26	-$3.77
68	5/0*5/2 - Fixed Picture Window	$2.40	$1.87	$1.34	$0.81	$0.28	-$0.25	-$0.78	-$1.31	-$1.84	-$2.37	-$2.90	-$3.43	-$3.96
69	5/0*6/2 - Fixed Picture Window	$2.45	$1.90	$1.35	$0.80	$0.25	-$0.30	-$0.85	-$1.40	-$1.95	-$2.50	-$3.05	-$3.60	-$4.15
70	6/0*3/2 - Fixed Picture Window	$2.50	$1.93	$1.36	$0.79	$0.22	-$0.35	-$0.92	-$1.49	-$2.06	-$2.63	-$3.20	-$3.77	-$4.34
71	6/0*3/10 - Fixed Picture Window	$2.55	$1.96	$1.37	$0.78	$0.19	-$0.40	-$0.99	-$1.58	-$2.17	-$2.76	-$3.35	-$3.94	-$4.53
72	6/0*4/6 - Fixed Picture Window	$2.60	$1.99	$1.38	$0.77	$0.16	-$0.45	-$1.06	-$1.67	-$2.28	-$2.89	-$3.50	-$4.11	-$4.72
73	6/0*5/2 - Fixed Picture Window	$2.65	$2.02	$1.39	$0.76	$0.13	-$0.50	-$1.13	-$1.76	-$2.39	-$3.02	-$3.65	-$4.28	-$4.91
74	6/0*6/2 - Fixed Picture Window	$2.70	$2.05	$1.40	$0.75	$0.10	-$0.55	-$1.20	-$1.85	-$2.50	-$3.15	-$3.80	-$4.45	-$5.10
Pricing Tables

Cell Formulas
Range	Formula
B40:N40	B40	=RANDBETWEEN(1,1.75)