Use table column names as range in INDEX() function

[Blue1971]

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]].

New formula for column B:
=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 the INDEX() function (instead of using explicit cell references)?

Thank you.

 

[Peter_SSs]

You will generally get faster responses if your provide sample data with XL2BB so that helpers do have to type it out to test.

Also, you are familiar with your data and what it means but readers are not. So how do we identify a "parent record" and a "child record" in your data?

Can you tell us in words what the formula calculation should be?

 

[Blue1971]

You will generally get faster responses if your provide sample data with XL2BB so that helpers do have to type it out to test.

Also, you are familiar with your data and what it means but readers are not. So how do we identify a "parent record" and a "child record" in your data?

Can you tell us in words what the formula calculation should be?

Thanks. I ended up figuring it out.

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", "")

 

[Peter_SSs]

Glad you got it sorted. Thanks for letting us know.