Something I never got about the Design of VLookup

[schielrn]

This is what I came up with for positives or negatives, but it could definitely be cleaned upo:

Function vlook(lookupValue As Variant, lookupRange As Range, columnNumber As Integer, Optional trueFalse As Boolean)
    On Error GoTo handler
    If columnNumber > 0 Then
        vlook = Application.WorksheetFunction.VLookup(lookupValue, lookupRange, columnNumber, trueFalse)
    ElseIf columnNumber < 0 Then
        vlook = Application.WorksheetFunction.Index(Range(Mid(lookupRange.Address, InStr(1, lookupRange.Address, ":") + 1, 10) & ":" & Mid(lookupRange.Address, InStr(1, lookupRange.Address, ":") + 1, 10)).Offset(, columnNumber + 1), Application.WorksheetFunction.Match(lookupValue, Range(Mid(lookupRange.Address, InStr(1, lookupRange.Address, ":") + 1, 10) & ":" & Mid(lookupRange.Address, InStr(1, lookupRange.Address, ":") + 1, 10)), 0))
    Else
        Exit Function
    End If
    Exit Function
handler:
    vlook = Evaluate("=na()")
End Function

It uses the vlookup function and index/match, but thats ok it made the code a little shorter in my opinion. You can still use a bigger table than is expected like with the original formula. So you can input:

=vlook(B2,E:H,4,0)
=vlook(B2,H:E,-4,0)
=vlook(B2,H:A,-4,0)

 

[Oorang]

Surely, if the table is more than two columns wide then the column number is needed? Or did I miss something?

A lookup table never needs to be any wider than the lookup column to the return column. As VLOOKUP assumes the lookup column is always leftmost, it should also assume the return column rightmost.

 

[PaddyD]

"A lookup table never needs to be any wider than the lookup column to the return column."

Indeed not, although I see no reason not to think that it's useful to be be able to write this sort of thing:

=vlookup(a1,lookup_table,(column()-1)*2,0)

...whereby incrementation of the (column()-1)*2 argument allows you to return alternate column results from a wide table-array without further changes to the formula. In my view, useful enough to warrant the need to specify which column you're after.

 

[Lewiy]

Personally, I think that’s it’s very useful having the column indicator there. If I want to return several items from a table that are not consecutive, I can write one formula that I can copy across. For example, if I want to pull data from every other column in the table, I’d use something like:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
=VLOOKUP("value",$A:$G,COLUMN(A1)*2,0)
<o> </o>
This would be much harder to achieve if the column argument was not there.
<o> </o>
Also, as previously mentioned, using named ranges makes this an important argument.

 

[litrelord]

I have to disagree as well. I use vlookup combines with column a lot for transferring data from one table to another. It may be expensive but as a quick and easy way to combine tables I find it invaluable.

Although the not looking to the left is extremely irritating at times. Although I know I can use a UDF or a combination of index & match I quite often just end up making a copy of the column I'm searching for and delete it again afterwards.

I'm all for formulas and code that's as efficient as possible but only when creating a model in excel which will be re-used. For an ad-hoc report it's whatever gets the job done quickest.

Nick

 

[Lewiy]

I'm all for formulas and code that's as efficient as possible but only when creating a model in excel which will be re-used. For an ad-hoc report it's whatever gets the job done quickest.

I completely agree. Clearly it’s more important to streamline applications and re-usable macros, especially of they are dealing with a lot of data, but if you are just chucking numbers around to create something off the cuff, then it doesn’t really matter how expensive your formulas are (unless you make the mistake I did of using SUMPRODUCT for five columns on 30,000 rows – that’s 450,000 formulas for those who are counting – this took so long to calculate that I gave up in the end!).

 

[cornflakegirl]

Paddy, Lewiy - thanks for the alternate column tip!

Can I ask - why is Index/Match better than Vlookup? I'd always assumed that Vlookup worked in the same way as if I constructed an Index/Match, and was just saving me a bit of typing...

 

[Domski]

why is Index/Match better than Vlookup?

To my mind I use VLOOKUP when I just want to return one column of data.

If, for example, I was going to return a number of columns from the same data table then using Index and Match is more efficient if you use the Match formula in a helper column to find the relative positions of the data to return and then a bunch of Index formula based on that.

It's more efficient that way as it's only doing the lookup part once rather than multiple Vlookups having to go through the process of locating the correct data in each individual formula. When you dealing with thousands of records the performance difference is very noticeable.

Dom

 

[cornflakegirl]

Thanks Dom.

 

[Richard Schollar]

I use INDEX/MATCH when the lookup column is on the right-hand side of the column containing the value I wish to return.