Dynamic Index Match Match with Indirect

[apzoe]

Hi

I'm trying to pull data from many sheets to a sheet called "Catalog", but I'm not getting the results I want.

I'm currently using this:

=INDEX(INDIRECT("'"&Sheet_Table[Sheet_List]&"'!A1:B7"),MATCH(A2,INDIRECT("'"&Sheet_Table[Sheet_List]&"'!A1:A7"),0),MATCH("Price",INDIRECT("'"&Sheet_Table[Sheet_List]&"'!A1:B1"),0))

I need it to pull the price of the items listed in "Catalog" from the sheet where the item is located.

 

[apzoe]

Can't link my demo file but here is the template.

https://docs.google.com/spreadsheets/d/1u4kAs97ELwlBrkR4GQGbsdXGtJuVXQXML4ypIaMb2Xc/edit?usp=sharing

 

[strooman]

I would create a range with the relevant sheet names.
Select that range and name the selection MyList.

Formula goes in B2 of Sheet Catalog.
=VLOOKUP(A2;INDIRECT("'"&LOOKUP(REPT("z";255);IF(COUNTIF(INDIRECT("'"&MyList&"'!A2:A9");A2);MyList))&"'!A2:B9");2;0)

Insert with Ctrl+Shift+Enter
And drag down

Sheet Catalog

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Product	Price					MyList
2​	aa	1​					Sheet1
3​	gg	8​					Sheet2
4​	Stone	4​					Sheet3
5​	Paper	11​
6​	kk	13​
7​	Glass	14​
8​	ll	15​
9​	ff	7​