Changing an indirect function to "Index / Match"

What's in E6, the name of the Table?

If E6 is the name of the table, than what you're doing can't be done without Indirect.

Yes E6 is the name of the table...

But what I've read here doesn't apply to table names ?

http://superuser.com/questions/46107...ntent-in-excel

To do it without Indirect, you'd have to explicitly name the table in the formula
=SUMIF(Table1[Produit],B6,Table1[Prix])

Unless the formula actually resides within the Table, then you don't need to name the table at all
=SUMIF([Produit],B6,[Prix])

I need a formula that will look in different tables depending on a cell value, so I will stick with indirect it seems !

Thanks for the help !

You're welcome.

The difference between your formula, and the formulas in the link you posted is
The formulas in that link are varying the 'Column' used within the table. They still have to explicitly supply the Name of the Table.
Your formula is varying the Name of the Table.

Yeah I though so.

Now do you know how I could simply make this formula work with text ?

=SUMIF(INDIRECT(E6&"[Produit]");B6;INDIRECT(E6&"[Prix]"))

I just realized some cell have text content and I'm not familiar at all with index or lookup functions !
(I don't need to add anything here, I just want the formula to pull specific cells that are in other tables)

Ok, so you're not 'really' wanting to SUM the values in Prix based on the match in Produit.
Instead, you just want to 'Return' the value in Prix corresponding to the row where B6 is found in Produit

Right?

Try
=INDEX(INDIRECT(E6&"[Prix]");MATCH(B6;INDIRECT(E6&"[Produit]");0))

Exactly, it works, thanks ! :D

Now my last challenge for this sheet

Basically I have different tables with the same products, with prices and other data (they are different suppliers)

Now I would like to have a formula that will check for the lowest price (prix) for a specific product (produit) in all the different tables and return a value (maybe the name of the table, but the lowest price itself might be quite easier).

Do you know which function would be best for this ?

I will make a new post for this since it's another matter, thanks