PDA

View Full Version : VLookup, how to reverse columns?



jungix
07-18-2006, 02:01 PM
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:

Application.VLookup(string, Columns("A:B"), 2, 0)

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

Application.VLookup(string, Columns("B:C"), 2, 0)

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
07-18-2006, 02:27 PM
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

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

matthewspatrick
07-18-2006, 03:15 PM
You can do:


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

mdmackillop
07-18-2006, 03:16 PM
Neat :clap: