INDIRECT w/ INDEX/MATCH for Named Ranges/Tables

[BeardedSith]

I had this working at one time but as all things go, I accidently deleted the formula and can't for the life of me remember what syntax I used to accomplish this task. Here's the code, the long version of this question will follow:

=IFERROR(INDEX(INDIRECT("'" & [@SheetName] & "'!" & "[Wolfs]"),MATCH([@First],(INDIRECT("'" & [@SheetName] & "'!" & "[Part]")),0)),"Not Found")

Long Version:
I have a sheet that I use for pricing updates on our eCommerce website. The owner sends me a spreadsheet that I input into a spreadsheet template, then I export all the necessary data from our eCommerce site. The sheet has the following columns on the "Updates" sheet (more than this, but this is the part that matters)"
COLUMNS:
Price
Retail
SheetName
First

ROWS:
1) 8.99, 9.99, meguiars, M-1808
2) 10.99, 12,99, meguiars, M-1990
3) 5.99, 12.99, meguiars, M-1991

On the second sheet I have:
COLUMNS:
Wolfs
Part
List

Without the INDIRECT in the formula, it works perfectly getting the exact data I need. The problem is having the named range as a lookup location. I can do INDIRECT("'" & [@SheetName] & "'!" & "A2") but the "[Wolfs]", etc. is causing #REF! errors. So I'm obviously missing some quotes or something somewhere. Thank in advance!

 

[BeardedSith]

I noticed that it's possible to output the range to these forums, so here ya go.

Pricing Changes.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Product ID	Product SKU	Product Name	Brand	Category String	OLDPrice	OLDRetail	Price	Retail	CHANGE	SheetName	First	NOTES
2	2830	100R	Marinco Threaded Sealing Ring	Marinco	Electrical/Adapters\; Plugs\; Outlets/Sealing	7.99	10.83	$7.99	$11.55	0	marinco	100R
3	2839	12VBP	Marinco ConnectPro Plug	Marinco	Electrical;Electrical/Adapters\; Plugs\; Outlets	16.99	24.73	$19.99	$28.25	x	marinco	12VBP
4	2840	12VCP	Marinco ConnectPro Receptacle and Plug	Marinco	Electrical;Electrical/Adapters\; Plugs\; Outlets	37.99	55.5	$41.99	$63.38	x	marinco	12VCP
5	2865	304EL-BCR	Marinco Contoured Power Inlet - Chrome	Marinco	Electrical/Adapters\; Plugs\; Outlets/Inlets	79.99	124.73	Not Found	Not Found		marinco	304EL-BCR
Updates

Cell Formulas
Range	Formula
H2:H5	H2	=IFERROR(INDEX(marinco[Wolfs],MATCH([@First],marinco[Part],0)),"Not Found")
I2:I5	I2	=IFERROR(INDEX(marinco[ List],MATCH([@First],marinco[Part],0)),"Not Found")
J2:J5	J2	=IFERROR(INDEX(marinco[ch],MATCH([@First],marinco[Part],0)),"")
L2:L5	L2	=IFERROR(LEFT([@[Product SKU]], FIND(" ",[@[Product SKU]])-1),[@[Product SKU]])

 

[KRice]

I was able to get this working, but I'm not sure that I made the correct assumptions. For example, this works:

=IFERROR(INDEX( INDIRECT([@SheetName]&"[Wolfs]"), MATCH([@First],INDIRECT([@SheetName]&"[Part]"),0) ),"Not Found")

But the syntax used here assumes that I have a named table...whose name is found in the SheetName column (and that is confusing). That named table could be anywhere within the Excel workbook. For this trial, I created a separate worksheet named ProdList, and on that ProdList worksheet, I included column headings of Wolfs, Part, List, and Ch because those headings are all used in the formulas on your main sheet.

Book1
	A	B	C	D	E	F	G
1	Part	Wolfs	List	Ch	Retail	Col7
2	304EL	4.97	ifs	ch 4 304el	9.99	extra
3	100R	8.97	ands	ch 4 100r	12.99	words
4	12VCP	11.97	buts	ch 4 12vcp	12.99	here
5
ProdList

See the main table here for an example of how the formulas look to access information found in the Table named "marinco" (which just happens to be on a different sheet called ProdList) using the INDIRECT function to construct the reference.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Product ID	Product SKU	Product Name	Brand	Category String	OLDPrice	OLDRetail	Price	Retail	CHANGE	SheetName	First	NOTES
2	2830	100R	Marinco Threaded Sealing Ring	Marinco	Electrical/Adapters\; Plugs\; Outlets/Sealing	7.99	10.83	8.97	ands	ch 4 100r	marinco	100R
3	2839	12VBP	Marinco ConnectPro Plug	Marinco	Electrical;Electrical/Adapters\; Plugs\; Outlets	16.99	24.73	Not Found	Not Found		marinco	12VBP
4	2840	12VCP	Marinco ConnectPro Receptacle and Plug	Marinco	Electrical;Electrical/Adapters\; Plugs\; Outlets	37.99	55.5	11.97	buts	ch 4 12vcp	marinco	12VCP
5	2865	304EL BCR	Marinco Contoured Power Inlet - Chrome	Marinco	Electrical/Adapters\; Plugs\; Outlets/Inlets	79.99	124.73	4.97	ifs	ch 4 304el	marinco	304EL
FirstSheet

Cell Formulas
Range	Formula
H2:H5	H2	=IFERROR(INDEX( INDIRECT([@SheetName]&"[Wolfs]"), MATCH([@First],INDIRECT([@SheetName]&"[Part]"),0) ),"Not Found")
I2:I5	I2	=IFERROR(INDEX( INDIRECT([@SheetName]&" [ LIST]"),MATCH([@First], INDIRECT([@SheetName]&"[Part]"),0)),"Not Found") [/LIST]
J2:J5	J2	=IFERROR(INDEX(INDIRECT([@SheetName]&"[Ch]"),MATCH([@First],INDIRECT([@SheetName]&"[Part]"),0)),"")
L2:L5	L2	=IFERROR(LEFT([@[Product SKU]], FIND(" ",[@[Product SKU]])-1),[@[Product SKU]])

 

[KRice]

I should have mentioned that my first table shown is named "marinco".
This is where my assumption may be incorrect. Are you trying to access information in a table whose name is shown in the SheetName column, or are you trying to access information found on a sheet named "marinco"?

 

[BeardedSith]

Yeah, I should have done a better job explaining that, sorry! The @sheetname is also the name of the named range/table on that sheet. I add, delete, and edit the other sheets all the time, so I make the named ranges within the sheets the exact same name as the sheet themselves. Just to make it all easier when I'm plugging in data.

 

[KRice]

Okay, thanks for that clarification. I think the formula syntax shown above should work then. Let me know if you run into any trouble getting everything working again.

 

[BeardedSith]

It worked perfectly, thanks! I kept trying to put an exclamation point or extra &'s in there.

 

[KRice]

You're welcome...glad to help. You could use either approach...either the Worksheet/Range method (with the "!") or the Structured Reference method, but based on some clues in your formula, it looked as if you were originally running with the SR method. You might consider changing the name of the SheetName column to something that clarifies that the table name appearing in that column is what matters.