Referring to names within Tables in index/match formula

[AnnaPoj]

Hi all

my first time here with a question I'm hoping you can help with.

I'm using index/match to pull data from Tables ... I'm comfortable with index/match .. it's the naming of the tables that I'm struggling with, and utilising those in formulae (I'm new to Tables)

This formula is working ... A37 contains a Header in the Table CCU, and concatenated with the Table Name of CCU it seems to work. B42 is the Part Number



=INDEX(INDIRECT("CCU["&A37&"]"),MATCH($B$42,CCU[Part],0))



What I'm trying to do is not code in CCU, but rather refer to another cell (A36) which has this CCU text in it. I've tried various combinations of Indirect, "" and & to no avail

Hope this question is clear, any help greatly appreciated.

AnnaPoj

 

[James006]

Hello,

Have you tested following :

=INDEX(INDIRECT(A36&"["&A37&"]"),MATCH($B$42,Indirect(A36&"[Part]"),0))

Hope this will help

 

[Domenic]

Try...

=INDEX(INDIRECT(A36&"["&A37&"]"),MATCH($B$42,INDIRECT(A36&"[Part]"),0))

Hope this helps!

 

[AnnaPoj]

Thanks so much team, I'd tried something similar but had obviously forgotten a couple of ""

Work perfectly, much appreciated

A

 

[James006]

You are welcome ...