You can LOOKUP on a sunny day, but does that mean you should?

Author: Carly Garratt – PASS Co-ordinator, Student Achievement Services, University of Central Lancashire

Not too long ago I had opened a couple of web pages, an e-book, and a Teams chat, hoping to figure out the art of VLOOKUP in Excel. VLOOKUP, I had learned, was an excel formula useful for finding specific content. If you’re interested (I was!) there’s a HLOOKUP too.

‘V’ is for vertical.

‘H’ is for horizontal.

I got there eventually, with the help of said resources, and it felt good to have figured out another of Excel’s little secrets. Here are the web pages for the VLOOKUP function, how to use VLOOKUP in Excel, and the e-book about Excel formulas. 

Once upon a time I had an internship manager who’d said that for every one thing you know about Excel, there are hundreds you don’t. It’s been on my mind ever since (five years and counting) and so everything I do with Excel feels like cracking a little bit more of its enigma.

Anyway, with this newfound* formula, I achieved what I had endeavoured; I could use VLOOKUP to search through a series of worksheets, looking for names of students, and automatically populating a register. Say what? VLOOKUP, put simply, is useful for copying data from one area to another. On the first sheet I have a full register featuring all names and dates. These are a combination of courses. Each sheet which follows separates the students into said courses. It’s these supplementary sheets which will be completed to say which person has been in attendance (or not). VLOOKUP will look at the correct worksheet (I could, for example, direct the formula to search the Maths sheet for a specific student on the 1^st September) and automatically copy the data about his attendance from the course-sheet to the main front sheet.

As the academic year progresses, I and my colleagues will update the supplementary sheets and the VLOOKUP function will automatically populate the main register. That’s great. Except…like a child who’d learned a new word and wanted to use it everywhere (mine was ‘simultaneously’, and I remember asking in my SATS test how to spell it – I can’t remember if it fitted the context of what I was writing, though) I had VLOOKUP in my grasp and I wanted to use it again.

So, I tried. I had my data (not a register this time – just a series of check boxes about whether tasks had been completed).

I had my formula.

And…it didn’t work.

Why?

It simply wasn’t necessary. It was ‘overkill’. It may have worked (I think), but do you know what else worked just as well? One of – if not the – most basic formulae: equals. Cell A3 on sheet ‘To Do!’ would be equal to whatever was noted in cell F12 on sheet ‘Archaeology!’ – either ‘yes’, ‘no’, or ‘some’. Excel didn’t need to LOOKUP this time. I could do that. Therein lies the age-old lesson – and as the scientists who created Jurassic Park discovered – just because it can be done, doesn’t necessarily mean it should be.

* Disclaimer: It wasn’t new to the world, and I didn’t personally find it, but I hope you get the idea.

Photo by Chinh Le Duc on Unsplash.