Index + Match + List name

[noeste]

Hello,

I need some advices regarding my formula Index - Match which use a name range.
When I use my formula with the name like this, I return correctly the value.

=INDEX(Volume_total;MATCH(A8&B8;Date&Customer;0))

I guess it’s because I write the name range directly in my formula.
But I use a list name and I would like to transform my formula in dynamic and related to the content of the cell of the list name.

I don’t find how return the name range from the choice of my list name instead write manually the name in the formula.
Is there a solution to make it possible in my formula ?

I have tried these formula but without success :

Test 1 : =INDEX(G7;MATCH(E8&F8;Date&Customer;0))

Test 2 : =INDEX(CELL("contents";C13);MATCH(A14&B14;Date&Customer;0))

I joined my excel sheet if someone could help me.

Thank you

 

[bosco_yip]

Try……….

1] In "Test 2" G8, Shift+Ctrl+Enter instead of just Enter.

=INDEX(INDIRECT(SUBSTITUTE(G7," ","_")),MATCH(E8&F8,Date&Customer,0))

2] In "Test 3" K8, Shift+Ctrl+Enter instead of just Enter.

=INDEX(INDIRECT(K7),MATCH(I8&J8,Date&Customer,0))

3] In "Test 4" C14, Shift+Ctrl+Enter instead of just Enter.

=INDEX(INDIRECT(CELL("contents",C13)),MATCH(A14&B14,Date&Customer,0))

p.s. changed the above formula "," to ";" in meet with your country setting

Regards
Bosco

 

[noeste]

Hi Bosco

Thank you, your proposals are exactly what I looking for.
Your solution for the test 2 is obvious, I blame myself for not thinking about use "Substistute" function.
However the test 3 and 4, I didn't know INDIRECT, I will learn how this function works, it seems very useful.