A Function That Is Self-Aware of Its Own Cell Address?

I would like to write a function that is self-aware of its own cell address. For instance, I would like to be able to place an argumentless function in any cell, and when called, that function will perform an operation based on the value in the cell immediately to its left.

I tried the following, which works great if the cell with the function in it is the active cell, but that won't be my case in general.

Function Test()  
    Test = Range(Application.ActiveCell.Address).Offset(0,-1)  
End Function

I'm guessing that there's an easy solution to this challenge, but it's only easy once you know it!!!

Thanks in advance!

(Excel 365)

Solution 1:

Don't use VBA if you don't have to.

=IF(relative_address=some_condition,{result if true},{result if false})

For example, if I want to show "Spooktacular" in H6 if G7 contains "Scooby Dooby Don't", or display nothing otherwise, then I would do this:

=IF(G7="Scooby Dooby Don't","Spooktacular","")

This formula can be copied to any other cell and it will always refer to the cell one column to the left and one row down. This happens because the address is written as G7 and not $G$7 (which would lock both the row and the column to that cell) or $G7 (which would lock the column to G) or G$7 (which would lock the row to 7).