Use table column names as range in index array?

Question

In an Excel 2016 table:

I have a formula that I use to check if parent records have the right 'Use With' values (if a child record has a 'Use With' value, then it's parents must have it too). More info here.

Column B =

IFERROR(IF(SUMPRODUCT(COUNTIF(INDEX( C:E, [@[Parent - RowNum]],0),Table1[@[Use With 1]:[Use With 3]]))<>COUNTA(Table1[@[Use With 1]:[Use With 3]]), "error", ""),"")

---

For example, if I were to delete the value in C2, the formula would successfully flag it as causing an error:

---

Question:

I'm trying to convert all explicit cell references —to— structured references (aka table column names). I want to do this to avoid some issues I've been having with adding/deleting columns in the spreadsheet (and because I assume it's best practice/cleaner).

I've tried to replace C:E with Table1[[Use With 1]:[Use With 3]].

=IFERROR(IF(SUMPRODUCT(COUNTIF(INDEX( Table1[[Use With 1]:[Use With 3]], [@[Parent - RowNum]],0),Table1[@[Use With 1]:[Use With 3]]))<>COUNTA(Table1[@[Use With 1]:[Use With 3]]), "error", ""),"")

However, when I do that, the formula fails to work correctly — it doesn't flag the problem rows with "error".

---

What's the correct way to use table column names as a range in an index array (instead of using explicit cell references)?

Answer 1

I just needed to add [#All], to the index array.

Table1[[#All],[Use With 1]:[Use With 3]]

Full formula:

=IF(SUMPRODUCT(COUNTIF(INDEX( Table1[[#All],[Use With 1]:[Use With 3]], [@[Parent - RowNum]],0),Table1[@[Use With 1]:[Use With 3]]))<>COUNTA(Table1[@[Use With 1]:[Use With 3]]), "error", "")