Is it possible to link an entry in a filtered sheet to the master sheet, matched on a key variable? (Excel)

Question

I am trying to link a column in a filtered sheet to another column in the master sheet using a key category (i.e., id). Below is the illustrated dataset:

[]

As shown above, I have the master sheet, and two filtered sheets based on the column "admission". To filter the entries based on admission, I used the FILTER function

=FILTER('Master sheet'!A$2:$D$20,'Master sheet'!$D$2:$D$20="accept",FALSE)

[]

=FILTER('Master sheet'!A$2:$D$20,'Master sheet'!$D$2:$D$20="reject",FALSE)

Note that I added notes to the filtered sheets. Is it possible to link the entries in the "notes" column of the master sheet based on id?

For example, I want to link the entry of column E2 in the Filter_Accepted sheet to column E2 in the Master sheet sheet based on the id 743. Part of the formula in column E of Master sheet should contain this:

=IF(Filter_Accepted!A2 = 'Master sheet'!A2, ...) *I can't figure out how to link the cells*

In the actual dataset, there are thousands of entries, and the entries change dynamically, so I don't think using = to link the cells will do the trick. The point is, the data from the "notes" column in the filtered sheets should be linked to the data in the Master sheet (matched on id), and when the entry in the Master sheet is deleted, the column will also be gone. Is this possible?

This is the desired output:

Thank you very much for the help

#p.s.: I can't use VBA since I'm using Excel Online

Answer 1

This isn't the most dynamic or elegant solution, but it should do the job for your current set-up. A formula for 'Master Sheet'!E2 that (I think) does what you want is this (a formula without structured references and named ranges is below).

The only deficiency I can think of here, is if you delete a row from the master sheet, that won't trigger the deletion of that particular row's notes cell in the filtered sheets, and so then the "notes" that you'd get would be off by one, since now a new ID is where that old ID was in the filtered sheet, but the new ID still has the old notes. I don't think there is a way to handle that particular scenario (at least, not without VBA), so I'd use this approach to get the master sheet cleaned up, and then use the master sheet, with filters, to do any future edits to notes and removals from the master list.

=IF(
    [@admission] = "accept",
    INDEX(
        Filter_Accepted!$1:$1048576,
        MATCH(
            [@id],
            INDEX(
                Accepted#,
                0,
                1
            )
            ,0
        )+1,
        5
    ),
    INDEX(
        Filter_Rejected!$1:$1048576,
        MATCH(
            [@id],
            INDEX(
                Rejected#,
                0,
                1
            ),
            0
        )+1,
        5
    )
)

Basically, we use an IF to search one of the two sheets, and use INDEX along with MATCH to get the cell that has the information we want. You can jump down to the bottom for a commented version that explains what the bits and pieces are doing.

You'll also notice I've got structured references and named cell ranges in here. [@admission] could be replaced with a relative reference to D2, [@id] could be replaced with a relative reference to A2, and the Accepted# and Rejected# named references could be replaced by Filter_Accepted:$A$2# and Filter_Rejected:$A$2#, respectively.

That formula would look like this:

=IF(
    D2 = "accept",
    INDEX(
        Filter_Accepted!$1:$1048576,
        MATCH(
            A2,
            INDEX(
                Filter_Accepted!$A$2#,
                0,
                1
            ),
            0
        )+1,
        5
    ),
    INDEX(
        Filter_Rejected!$1:$1048576,
        MATCH(
            A2,
            INDEX(
                Filter_Rejected!$A$2#,
                0,
                1
            ),
            0
        )+1,
        5
    )
)

Explanation of Formula

=IF(
    ' Check whether master table admission column, for this row (@), is accept
    [@admission] = "accept",
    ' it is accept. Use INDEX to get the cell with the notes for
    ' this row's id
    INDEX(
        ' search the entire Filter_Accepted sheet
        Filter_Accepted!$1:$1048576,
        ' find the row of the Filter_Accepted sheet that has the relevant ID.
        MATCH(
            ' the ID we're searching for.
            [@id],
            ' where we're searching for that ID. In this case,
            ' we're looking at all rows (0), of the first column (1),
            ' of the dynamic array returned by the `FILTER` for accept.
            INDEX(
                Accepted#,
                0,
                1
            )
            ' Exact match (0)
            ,0
        ' the row number we get will be off by one,
        ' since the `FILTER` dynamic array is in Filter_Accepted:$A$2.
        ' so, add 1 to get the right row number for the whole sheet.
        )+1,
        ' we want the fifth column of the Filter_Accepted sheet, since
        ' that's the one that has the notes.
        5
    ),
    ' it is not accept, so it must be reject
    INDEX(
        ' same story as above, just for the Filter_Rejected sheet
        ' and the dynamic array from the `FILTER` for reject.
        Filter_Rejected!$1:$1048576,
        MATCH(
            [@id],
            INDEX(
                Rejected#,
                0,
                1
            ),
            0
        )+1,
        5
    )
)

Example File

Can be found here: https://www.dropbox.com/s/5anilsphdlloh60/superuser-1716190.xlsx?dl=0 (both versions of the formula should work)