Something I never got about the Design of VLookup

[Greg Truby]

OK - since ain't nobody up an' said it, I will. Like Richard I'll use VLookup() when fetching ta the right and Index(Match()) when fetchin' left. What I won't do, is to pop in a UDF - in spite of the ease of writing one. Mainly because of the VBA security crapola. Yes, I could digitally sign the workbook. Yes, I could have the user install my digi-sig if he ain't got it (assuming, of course, that his security is set to medium and not high or VH). Or, I could put the code in a custom add-in, that would at least make it more "accessible" to the workbook. But then I'd have to distribute the add-in. And all of this to avoid using Index(Match())? Blech!

Now I'll get off'n the soapbox and just nod my head in that yes, I too cannot figure out why the fellers that wrote it didn't let 'er look ta the right 'n' fetch from the left too.

The other burr under my saddle is having ta specify the entire range yer lookin' into. More'n once I've gotten a blasted #REF! error 'cause I want a column that's off the right end of my "table_array" and I gotta go back and widen 'er up a bit. A while back I figured out that this "pass in the whole table" bit would be necessary for the function to process "hard-coded" arrays, in addition to ranges. But I woulda hoped the fellers that wrote the function coulda made 'er smart enough to have it behave differently based on the type of input received. ['course considerin' the thing don't even "fetch left", I reckon I ought not to be surprised. ]

 

[Domski]

Match does have the advantage of having the 3 match options (nearest lower, exact or nearest higher) which Vlookup doesn't. Another ommission in my opinion.

Dom

 

[Oorang]

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.

Yes but you could still have that functionality by changing the table reference instead of the column:

=SVLOOKUP(A1,INDIRECT("Sheet2!$A$1:"&ADDRESS(50,((COLUMN()-1)*2))))


But that, said some people have a few good arguments about why it should stay. I think I like the suggestion that it just be optional and default to rightmost column, then you could have your cake and eat it too.

 

[Jonmo1]

Another very useful reason to have the colref in vlookup that I don't think has been touched on...So it can vary dependant upon user entry...

Say you have a vlookup of a part number, and Columns for Price/Color/Weight/Bin Location/whatever...

you can make your Colref Variable depending on a user entry in another cell, if they want to view the different properties of the part number.


Again, this is still better obtained with Index/Match, but the discussion is about Vlookup....

 

[Oorang]

That's a pretty compelling argument. You could then link the to a combobox index, etc then they could just select the column of the combo box and the formula would pull the data.... But wait... You could STILL do that by just changing the table reference dynamically instead of the column number!


Although to be fair, a dynamic table reference is conceptually less accessible to a new user so it would steepen the learning curve.

 

[Jonmo1]

Maybe our argument should be...

Why do we need Vlookup at all ?

 

[schielrn]

Why do we need Vlookup at all ?

That would be a very good argument as many have said that they use index/match instead of vlookup for many of different reasons. But with beginners I find it much easier to teach them vlookup as opposed to using Index and Match.

 

[Jonmo1]

But with beginners I find it much easier to teach them vlookup as opposed to using Index and Match

And there is the answer to the Question in the OP.

I still say the best way to please everone would be to make the ColRef Optional, and default to the rightmost column. But I'm not a developer at Microsoft....

 

[Oaktree]

While I agree that vlookup is zoolander flawed in that it can't look to the left ...

Am I the only one who gets that reference?!? VLOOKUP is not an ambiturner!

Remember that not every Excel user lives and breathes the stuff like us. Many of them are the =SUM(A1+A2) type. For casual users, having an easy way to get a result is well worth a few milliseconds of efficiency. VLOOKUP might take some time to learn, but Mr. T pities the fool who tries to explain dynamic arrays and INDEX/MATCH to a =SUM(A1+A2) loyalist.

As another example, sometimes it's just easier to tell someone to use ISEVEN than it is to explain the concept of modulus.

 

[Jonmo1]

Am I the only one who gets that reference?!? VLOOKUP is not an ambiturner!

Not anymore, I get it now...

ROTFLMAO !!!!!