New to VBA (and generally a lousy programmer).
I wrote this function based on a YouTube vid.
It works and I use this UDF in multiple worksheets in an Excel file. Problem is if the function updates in one worksheet it affects the function in another worksheet.
Excel built-in functions preserve their original argument cell references - but obviously I'm missing something.
Any help?
[VBA]
Function ReverseLookup(MatrixValue As Range, LookupTable As Range)
HRow = LookupTable.Rows(1).Row - 1
HCol = LookupTable.Columns(1).Column - 1
ReverseLookup = ""
For Each cell In LookupTable
If cell.Value = MatrixValue.Value Then
ReverseLookup = ReverseLookup & Cells(HRow, cell.Column).Value & "," & Cells(cell.Row, HCol).Value & ", " & cell.Value
End If
Next cell
End Function
[/VBA]