VLookup, how to reverse columns?

[jungix]

Hi,

According to the VBA help, you have to look for a value in the first column of your range with VLookup.

I need to look a value in column A and find the corresponding value in column B, which I do thanks to:

[VBA]Application.VLookup(string, Columns("A:B"), 2, 0)[/VBA]

Then I need to do it the otherway, and the only way I found yet is to copy column A in column C and use

[VBA]Application.VLookup(string, Columns("B:C"), 2, 0)[/VBA]

Is there a way to do it directly looking for a value in column B and taking the value in front in column A using Vlookup?

[mdmackillop]

Is there a way to do it directly looking for a value in column B and taking the value in front in column A using Vlookup?

No, so try
[VBA]
Sub lookup()
Dim a
Dim str As String
str = "Test28"
Set a = Range([A1], [A1].End(xlDown))
Set b = Range([B1], [B1].End(xlDown))
MsgBox a(Application.WorksheetFunction.Match(str, b, 0))
End Sub

[/VBA]

[matthewspatrick]

You can do:

[VBA]
With Application
MyVar = .Index([a:a], .Match(LookFor, [b:b], 0), 1)
End With
[/VBA]

[mdmackillop]

Neat