VLOOKUP? But Chris, that’s easy!

Well, perhaps it is – for you.  And, if you’re applying the formula to textual or ‘discrete’ data.  I start today’s video by demonstrating a typical – and straightforward – application of VLOOKUP.  If you’re just getting started or need a refresher on this essential Excel formula, this is where to start …

Members' Monday FREE 1-hour taster session

Download File HERE

CLICK HERE TO WATCH THE VIDEO

Before we go further, let me tell you about one of the most common enquiries we receive here at Tiger.  It concerns classification of grades in an educational context.  Somebody (usually an overworked teacher) wishes to convert a number (eg. 65) to a classification (eg. ‘A’, ‘B’, ‘C’) – and can’t find an easy way to do it in Excel.  I’ve answered this question so many times over the years!

Why is the answer so difficult to find?  It’s because, in my view, it involves a little-known ‘mode’ in VLOOKUP.  Have you spotted this mode before?

It concerns the 4th ‘argument’ – or part of the formula – in VLOOKUP which Excel describes, somewhat unhelpfully, as ‘range_lookup’.  Like many, you’ve probably never taken much notice of this 4th argument, having become accustomed to setting it to ‘0’ or ‘false’, which is what you should do if working with text.  But, what happens if you set it to ‘1’ or true?

TIMESTAMPS

00:00 Easy VLOOKUP Demo

00:54 Testing Basic VLOOKUP

01:03 When To Use VLOOKUP’s Secret Mode

01:35 Building VLOOKUP In Secret Mode

02:43 Understanding How Boundaries Work With VLOOKUP And Approximate Match

04:00 About Our Members’ Monday Community

Well, if you’re working with text (and the entries in the lookup table are sorted in alphabetical order), the answer is ‘not much’!  If you’re working with numbers, however, this new mode might unleash a world of possibilities.  You see, in VLOOKUP’s ‘secret’ mode, Excel whirs away in the background, creating what Excel calls ‘implied ranges’.  Another less than useful description!  Yes, so let’s think of them as something different: categories.

Yes, VLOOKUP’s secret mode gives us the ability to numerically define ‘categories’ in Excel, using a simple VLOOKUP formula.  In today’s video, I explain how to define the limits of the categories using a single cell entry as a ‘lower bound’.  Don’t worry, all is explained in the video, and I demonstrate various input values to help build your understanding.  I hope you’ll develop the confidence to harness VLOOKUP’s secret mode in your projects – and it’s an absolute must for teachers!

Let me know how you get on in the YouTube comments – I’ll get back to you there.

ABOUT MEMBERS' MONDAY

This video is taken from our unique and exclusive Members' Monday learning community.

Looking to build your data analysis skills in a supported environment with expert access, with a group of like-minded individuals?

Need structured materials with a practical focus, and a place to go for help?

Want to take a long-term approach to your learning and get real improvement, rather than lurching from one problem to the next?

You'll love our Members' Monday community!

"I had a massive breakthrough this morning solving a problem with a file I have been unhappy with for seven years!" - RECENT MEMBER FEEDBACK

Members' Monday FREE 1-hour taster session