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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#0070C0"]Product[/td][td="bgcolor:#0070C0"]Price[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#4472C4"]MyList[/td][/tr]
[tr][td]

2​

[/td][td]aa[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#D9E1F2"]Sheet1[/td][/tr]
[tr][td]

3​

[/td][td]gg[/td][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td]Sheet2[/td][/tr]
[tr][td]

4​

[/td][td]Stone[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#D9E1F2"]Sheet3[/td][/tr]
[tr][td]

5​

[/td][td]Paper[/td][td]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]kk[/td][td]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]Glass[/td][td]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]ll[/td][td]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]ff[/td][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]