reverse vlookup

[bewsh1987]

Hi

I need someone's help I have the below function that allows me to perform a vlookup in reverse which is great however, when I try to use a data on a different worksheet or workbook I am finding it hard to reference the workbook name or worksheet name as it will always give me the range only

any ideas on how I can get the name from the selected range

Function RVlookup(a As Variant, b As Range, c As Variant)
    
    My_Lookup_Value = a
    My_Lookup_Range = b.Address
    Offsetcol = c
    
    Debug.Print My_Lookup_Range
    strt_col = Range(My_Lookup_Range).Columns.Column
    end_col = Range(My_Lookup_Range).Columns.Count + (Range(My_Lookup_Range).Columns.Column - 1)
    strt_row = Range(My_Lookup_Range).Rows.Row
    end_row = Range(My_Lookup_Range).Rows.Count + (Range(My_Lookup_Range).Rows.Row - 1)
    
    MatchAddress = Cells(strt_row, end_col).Address & ":" & Cells(end_row, end_col).Address
    Debug.Print MatchAddress
    my_match = Application.Match(My_Lookup_Value, Range(MatchAddress), 0)
    
    val1 = end_col - strt_col + 1 - Offsetcol + 1
    
    log3 = Application.Index(Range(My_Lookup_Range), my_match, val1)
    Debug.Print log3
    RVlookup = log3
    
    If Offsetcol > end_col Then RVlookup = CVErr(xlErrRef)

End Function

 

[Jonmo1]

Instead of creating new range variables in the function, You need to actually use the range variables setup by the function itself (a and b).

Give this a try

Function RVlookup(a As Variant, b As Range, c As Variant)
Dim ColCount As Long, MatchRow As Long
ColCount = b.Columns.Count
If c > ColCount Then
    RVlookup = CVErr(xlErrRef)
    Exit Function
End If
MatchRow = Application.Match(a, b.Columns(ColCount), 0)
RVlookup = Application.Index(b, MatchRow, ColCount - c + 1)
End Function

Now it will be able to use whatever sheet/book is referenced when you actually call the function
=RVlookup(Sheet1!H1,Sheet1!A1:D5,2)

 

[bewsh1987]

I always try to over complicate my work

Thank you so much work perfect

 

[Jonmo1]

You're welcome.

Also, this is really kind of re-inventing the wheel, so to speek.
There are buit in functions that can do a backwards vlookup.

Take a basic vlookup
=VLOOKUP(A1,B:C,2,FALSE)
We know that finds A1 in column B, and returns the corresponding value from C.

But we want the reverse, find A1 in C and return corresponding value from B.

A very common and simple way to do that is with Index and Match instead of Vlookup.

=INDEX(rangetoreturnvaluefrom,MATCH(valuetomatch,rangetofindmatch,0))
=INDEX(B:B,MATCH(A1,C:C,0))

Hope that helps.