Index Match With Tables Not Working

DeaninGA

Board Regular
Joined
May 20, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 20, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Attachments

  • Mr Excel example.png
    Mr Excel example.png
    36.8 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
In that case try
Excel Formula:
=INDEX(Table13,MATCH([@[Frame Only]],Table13[Window Size],0),MATCH([@[SDL Light]],Table13[#Headers],0))
 

DeaninGA

Board Regular
Joined
May 20, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

In that case try
Excel Formula:
=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
 

Attachments

  • MREXCEL2.png
    MREXCEL2.png
    5.7 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
It's probably the * which is being treated as a wildcard. Change both Match functions to XMATCH
 

DeaninGA

Board Regular
Joined
May 20, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 20, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
ABCDEFGHIJKLMNOPQRST
1Order Total#N/A
2
3LineFrame OnlyGlass TypeLow EJamb Exterior TrimCasingSDL LightTransomsWindow Base PriceGlass Add on PriceLow E Cost Add onJamb Price add onTrim Pricing Add OnCasing Price Add On SDL Add OnTransom Price Add onSum EachQTYLine Total
412/0*3/2- Double HungClearYes4-9/165/4*4544F8Transom 1$10.00$12.00$10.00$0.90$1.75$0.98#N/A$0.00#N/A1#N/A
522/0*5/2- Double HungClearYes4-9/165/4*4546PR2Transom 1$13.00$15.00$16.00$0.96$1.78$1.10#N/A$0.00#N/A2#N/A
632/0*3/2- Double HungGBGYes6-9/161*4546F8Transom 3$10.00$15.00$10.00$1.25$1.50$0.98#N/A$0.00#N/A4#N/A
743/0*6/2- Double HungGBGNo4-9/161*4P142816Transom 2$29.00$24.500$1.28$1.69$1.74#N/A$0.00#N/A2#N/A
85  0   #N/A$0.00#N/A#N/A
96  0   #N/A$0.00#N/A#N/A
107  0   #N/A$0.00#N/A#N/A
118  0   #N/A$0.00#N/A#N/A
129  0   #N/A$0.00#N/A#N/A
1310  0   #N/A$0.00#N/A#N/A
1411  0   #N/A$0.00#N/A#N/A
1512  0   #N/A$0.00#N/A#N/A
1613  0   #N/A$0.00#N/A#N/A
1714  0   #N/A$0.00#N/A#N/A
1815  0   #N/A$0.00#N/A#N/A
1916  0   #N/A$0.00#N/A#N/A
2017  0   #N/A$0.00#N/A#N/A
2118  0   #N/A$0.00#N/A#N/A
2219  0   #N/A$0.00#N/A#N/A
2320  0   #N/A$0.00#N/A#N/A
2421  0   #N/A$0.00#N/A#N/A
2522  0   #N/A$0.00#N/A#N/A
2623  0   #N/A$0.00#N/A#N/A
2724  0   #N/A$0.00#N/A#N/A
2825  0   #N/A$0.00#N/A#N/A
Tool
Cell Formulas
RangeFormula
S1S1= SUM(Table7[Line Total])
J4:J28J4=IFNA(XLOOKUP([@[Frame Only]],Table6[[#All],[Window Size]],Table6[[#All],[Base Price]],,0,1),"")
K4:K28K4=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:L28L4=IF([@[Low E]]="Yes",VLOOKUP([@[Frame Only]],Table6[#All],7,FALSE),"0")
M4:M28M4=IFERROR(VLOOKUP([@[Frame Only]],Table6[#All],IF([@[Jamb ]]="4-9/16",9,IF([@[Jamb ]]="6-9/16",10,""))),"")
N4:N28N4=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:O28O4=IFNA(VLOOKUP([@[Frame Only]],Table6[#All],13,FALSE),"")
P4:P28P4=INDEX(Table13,XMATCH([@[Frame Only]],Table13[Window Size],0),XMATCH([@[SDL Light]],Table13[#Headers],0))
Q4:Q28Q4=IFERROR(VLOOKUP([@Transoms],#REF!,2,FALSE),0)
R4:R28R4=SUM(Table7[@[Window Base Price]:[Transom Price Add on]])
T4:T28T4=[@[Sum Each]]*[@QTY]
Cells with Data Validation
CellAllowCriteria
B4:B28List='Data Validation Lists'!$A$3:$A$37
C4:C28List='Data Validation Lists'!$C$3:$C$6
D4:D28List='Data Validation Lists'!$M$3:$M$4
E4:E28List='Data Validation Lists'!$E$3:$E$4
F4:F28List='Data Validation Lists'!$G$3:$G$5
G4:G28List='Data Validation Lists'!$O$3:$O$14
H4List='Data Validation Lists'!$I$3:$I$15
I4:I29List='Data Validation Lists'!$K$3:$K$6
H5:H28List='Data Validation Lists'!$I$4:$I$15
 

DeaninGA

Board Regular
Joined
May 20, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Test Pricing tool.xlsx
ABCDEFGHIJKLMNOPQ
39Window Size0234689121618243648Transom 1Transom 2Transom 3
402/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
412/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
422/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
432/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
442/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
452/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
462/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
472/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
482/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
492/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
502/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
512/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
522/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
532/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
542/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
553/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
563/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
573/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
583/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
593/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
604/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
614/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
624/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
634/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
644/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
655/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
665/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
675/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
685/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
695/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
706/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
716/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
726/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
736/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
746/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
RangeFormula
B40:N40B40=RANDBETWEEN(1,1.75)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,297
Members
417,135
Latest member
zeusmining

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top