Autofilter data in "Tab 2" from "Tab 1" using a VBA function

I have written a function that accepts 3 arguments and filters the "Overrides v2" tab to extract the 1 related value. It works fine with the VBA editor's Immediate window but fails to execute the filter when called from the formula of a cell in the calling tab.

"Overrides v2" contains 6 columns: Style, Duplicated, WarehouseSKU, Market, ColumnToUpdate, Value.

This is how I filtered:

Sheets("Overrides v2").Range("Table_Overrides_v2").AutoFilter Field:=3, Criteria1:=WhSearchSku
Sheets("Overrides v2").Range("Table_Overrides_v2").AutoFilter Field:=4, Criteria1:=Market
Sheets("Overrides v2").Range("Table_Overrides_v2").AutoFilter Field:=5, Criteria1:=Col2Update
.
.
.
' The last element of the array is the cell address of the value to return
WhRange = Split(Sheets("Overrides v2").UsedRange.SpecialCells(xlCellTypeVisible).Address, ":")
If UBound(WhRange) > 1 Then Override_Value = Sheets("Overrides v2").Range(WhRange(UBound(WhRange))).Value

I commented out the "showalldata" statement to allow me to switch to observe the "Overrides v2" tab where I found the filters did not take - no columns where filtered. "Debug.Print" statements displayed the full address range of "Table_Overrides_v2" rather than the row of the target data.

Why am I able to filter within the context of the VBA editor but not within the context of a calling formula?

I would have used a multi-column XLOOKUP except this same process will be used on 30+ columns across several tabs, so a function makes more sense than hardcoding XLOOKUP statements throughout the workbook. That way, updates applied to the function will affect all calling cells.

How can I make this work? Is there a better implementation?

If your version of excel supports it, you can use a LAMBDA.

Here's a basic example:

Creating a LAMBDA: https://support.microsoft.com/en-au/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67