Something I never got about the Design of VLookup

[HalfAce]

“If I sit with you for five minutes, can you please show me how to all the things you do in Excel? I can do formulas like SUM and even AVERAGE so I’m well on my way…………..what’s a macro?”

Yeah, similarly, one of my favorite questions is:
"Hey, can you teach me Excel?". . .

 

[RoryA]

I would have been happy with:
=VLOOKUP(lookup_value,lookup_column_range,return_column_range,match_type)
possibly with an optional 5th argument of what to return if no match.

 

[gingerafro]

I'll firstly admit that I don't understand exactly how formulas work and therefore what performance issues there are, but what would be the arguments against the formula which on the face of it appears simpler than INDEX/MATCH:

=SUMPRODUCT(--($B$1:$B$10000=E1),($A$1:$A$10000))

to do a 'left lookup' on values entered in the E column.

 

[RoryA]

That would require the return value to be a number and for there to only be one match. You also wouldn't be able to use the equivalent of the TRUE argument in VLOOKUP.

 

[gingerafro]

I get that, thanks.
Most of my Vlookups are to return a value for an exact match in a list of unique items, which is why I hadn't considered those pitfalls.
I did try my example using a long list and the performance is terrible. So the answer is in theory it does work, but only in certain situations and for small ranges.

Therfore is no substitute for INDEX/MATCH. I was just trying to find an easier way to write a 'Left Lookup' that my colleagues who are infrequent excel users could copy.

 

[schielrn]

Ok that got me thinking maybe you could use lookup to look left if you know there is always going to be an exact match and unique values, but it did something wierd when there are not unique values? Can anyone possibly explain why this happened?

Book1
	A	B	C	D	E
1
2
3
4
5	rt	5
6
7	eddir	5			eddir
8					rob
9
10
11
12	rob	5
Sheet1

E7 and E8 are both looking up 5 within B1:B12 and returning the value from A1:A12. Now one uses a whole column reference and returns the last value found. And the one that uses the exact range returns the 2nd value found? And if I add another 5 and name in A9:B9 the full column reference the 3rd one is returned and not the last one anymore?

Book1
	A	B	C	D	E
1
2
3
4
5	rt	5
6
7	eddir	5			eddir
8					steve
9	steve	5
10
11
12	rob	5
Sheet1

Now I don't use lookup all that oftern, so I don't necessarily completely understand it, but I cannot figure out a patter on how it is actually looking these up? I know the range normally has to be sorted for it to work properly, but I am not using different values. Can anyone kindly explain this to me?

Thanks.

 

[cornflakegirl]

You're not using different values, but you do have blank cells. If you put values in the other cells, it will pull back the last one.

(It is odd though!)

 

[Deano]

Interesting one.

If you get a little creative with the OFFSET function, you can combine it with the vlookup value to decide if you really want VLOOKUP to start at the first column of your table. The same can be applied to the column number as this could also be variable.

Regards

 

[Angelicus]

While we’re on the subject, here’s a classic quote from a colleague earlier this week:
“If I sit with you for five minutes, can you please show me how to all the things you do in Excel? I can do formulas like SUM and even AVERAGE so I’m well on my way…………..what’s a macro?”

If this quote is not good enough I heard another one from a staff working in an embassy. When she applied for courses in Excel, her colleague replied. "Even my 8 years old daughter knows everything about Excel already!"

Well, I cannot instantly doubt this possibility