Return value of a row in a column nested

[Belinda]

I tried searching, but can’t seem to find the exact circumstance, so I’m reaching out here. Please bear with me I’m using the TableName[ColumnName] format for clearer understanding

short version:
need all row values of TableC[INV Num] when:
value of row TableC[TableB KEYID] equals “X”

“X” equals the TableB[KEY ID] when TableA[KEY ID] equals “Y”
“Y” equals the TableA[KEY ID] when on [_THISROW]

TL/DR:
I have 3 tables. In TableA, I am trying to get the values of every row in Table C where the value of a certain column matches the value of a column in TableB, which in turn, matches a column in TableA.

I’ve made a sample of how this all translates back into Excel, and I just need a little help figuring out the expressions to use. The real tables have multiple columns that aren’t represented here because they are unimportant to the issue at hand. Thank you!

[Steve · Accepted solution]

The App formula expression for INV NUM column of TableA (untested):

SELECT(
  TableC[INV NUM],
  IN(
    [TableB KEYID],
    SELECT(
      TableB[KEY ID],
      ([TableA KEY ID] = [_THISROW].[KEY ID])
    )
  ),
  TRUE
)

1. SELECT(TableC[INV NUM], ..., TRUE) gathers a List of distinct INV NUM column values from TableC that match the given criteria (..., see (2)). If you don’t want distinct values, preferring duplicates be preserved when they occur, omit TRUE or replace with FALSE.

2. IN([TableB KEYID], ...) selects only those rows with a TableB KEYID column value that occurs in the given list (..., see (3)).

3. SELECT(TableB[KEY ID], ...) gathers a List of KEY ID column values from the TableB table that match the given criteria (..., see (4)).

4. ([TableA KEY ID] = [_THISROW].[KEY ID]) selects only those rows with a TableA KEY ID column value that matches this row’s KEY ID column value. As the entire expression (1) is run from TableA, “this row” refers to a row in TableA. If the TableA KEY ID column value in TableB might be blank, wrap ([TableA KEY ID] = [_THISROW].[KEY ID]) with AND(ISNOTBLANK([[TableA KEY ID]]), ...).

See also: IN(), SELECT()

[Belinda]

Thank you very much, I will give that a whirl!!

[Belinda]

Thank you Steve, it worked like a charm