Something I never got about the Design of VLookup

[Jonmo1]

You know the exact same argument can be made for Hlookup. Just like Vlookup can't read left, Hlookup can't read up (event though it's called Hlookup )

In fact, I'd bet (not the house or anything) that half of the people who do use Vlookup, probably use Index/Match instead of Hlookup...

 

[NateO]

... a =SUM(A1+A2) loyalist.

That's great!

 

[Greg Truby]

...Many of them are the =SUM(A1+A2) type...

Only ever saw that done once. She's been promoted four times in eight years.

 

[PaddyD]

while I think of it, another useful consequence of being able to specify the column argument:

=vlookup(a1,table_array,{2,3,5,7},0)

...array-entered into a 1*4 range

 

[Oorang]

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

Hahahaha! I didn't make the ambiturner connection I was just thinking "as stupid as zoolander". Should have expected more from MM

... a =SUM(A1+A2) loyalist.

If have trained so many people in VLOOKUP it's not funny, and I have yet to get someone to really understand it without drawing them a picture. Now it's like "Will you show me how to use VLOOKUP?" and I just automatically have them get a paper and pen.

 

[Oorang]

I even give them a little fill in the blank sentence:
Me: "Lookup blank in table blank. And if you find it, give me column blank from the row you found it in."
Them: "What is that last box for?"
Me: "For now it's always zero."

Of course you still have to spend time explaining the difference between a table and a worksheet, that #N/A! doesn't mean you did anything wrong, and that numeric 1 is not the same as text "1". Oh and 0 means you found a blank cell.

 

[Lewiy]

I find my biggest problem is explaining to people that they need to make sure they don’t have any preceding or trailing blanks in their data, otherwise VLOOKUP will not work (or INDEX/MATCH for that matter). They seem to not realise the importance of accurate data entry and assume that things will “just work”. When they don’t work, they decide immediately that “complicated formulas” are not worth the effort and spend hours doing things manually instead!!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
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?”

 

[litrelord]

“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?”

Know that feeling. Before changing departments my previous supervisor asked me to write up an explanation of the macros they still used with 'detailed instructions showing how to update them is necessary'. I told them if I could do that I'd be publishing Excel help books not working here.

(VLOOKUPs are very useful - just thought I'd mention them in case I get accused of an off-topic post)

Nick

 

[mortgageman]

Hahahaha! I didn't make the ambiturner connection I was just thinking "as stupid as zoolander". Should have expected more from MM

That's me - I troll these boards making sure that all cultural references are at a (G-d help us) elevated level.

 

[Cbrine]

One things I don't think anyone has mentioned, is an issue I encountered a few times. Mostly my own fault using Index(Match()). I've had occasions where I've misaligned the ranges in the index(Match()) and ended up with results that looked OK, but were totally wrong. I realize a little validation on my part would have found the problem, but this is not something that can happen with a vlookup, since it's only a single range.