I have a table with several columns in Sheet A. On Sheet B, I have a cell with a dropdown list that refers to a named range. This named range is defined as:

=OFFSET(Table!$E$8,0,0,COUNT(IF(Table[ID1]="","",1)),1)

Table!$E$8 is the first data point in the column [ID1]. This formula currently retrieves the entire contents of the column, excluding blanks, when used as a data validation dropdown.

I need to add multiple criteria (two to be exact) to this dynamically defined range such that it only produces a list where the data in ID1 match two other points in two other columns on the table. The multiple criteria is sourced from two prior dependent lists that are already set up.

I have tried combining INDEX, MATCH, and OFFSET into this formula. I have tried NESTED IF's. I have tried & and AND(). All to no avail.