Using INDEX & MATCH instead of VLOOKUP

I’ve been using Excel since Office 97. I was about 8 years old, and had no idea what I was doing. By the time I had progressed to VLOOKUPs and PivotTables, I was a bit older (and wiser, I’d like to think).

It was to my chagrin that I discovered about 2 years ago, that instead of creating a clusterbomb of VLOOKUP/CHOOSE (sometimes with some nested IFs sprinkled in for good measure), I could have been doing INDEX/MATCH all along.

=INDEX(x, MATCH(y, z, 0))

x = Range to return a value from
y = Value to look up
z = Range to look up the value in

I’ve made a small spreadsheet to demonstrate. Please ignore the fact that copying the formulas from Excel Online has changed them to the American region structure, hence the semi-colons as delimiters instead of commas.

Cell E2 does the standard VLOOKUP

=VLOOKUP(D2;$A$2:$B$9;2;FALSE)

while F2 uses INDEX/MATCH to achieve the same thing

=INDEX($B$2:$B$9; MATCH(D2;$A$2:$A$9;0))

That looks a bit more complex than the VLOOKUP. The real power is demonstrated in the next example. To achieve E3, VLOOKUP does the following

=VLOOKUP(D3; CHOOSE({2\1};$A$2:$A$9;$B$2:$B$9);2;FALSE)

while INDEX/MATCH follows the same structure as before in F3

=INDEX($A$2:$A$9; MATCH(D3;$B$2:$B$9;0))

This is why I am now more inclined to use INDEX/MATCH. The ability to do a lookup in any column, and return a value from any column, is great. This really helps for those times when I’m not just rather loading the data from the spreadsheets into Python dictionaries and pulling it apart there.

OneNote: The Future

Having OneNote accessible everywhere is great. From starting out by storing the Notebook on my flash, to now having all my Notebooks on my OneDrive syncing across my work laptop, my home PC, my tablet and my Nokia Lumia 1020, I always have access to my notebooks. The only issue I have with OneNote is that I wish it had been advertised more, as it would have helped me so much when I was studying between 2007 – 2010. As it is, when I start studying part-time, OneNote will be front and centre.

OneNote is the first application I open when I start up my laptop. I use the OneCalendar add-in when I fill in my timesheets to see which project I worked in on which day. I’ve gotten most of my colleagues into using it to various degrees. I have a Notebook for my personal matters, one for this blog containing ideas for posts, and one from the Family Room messenger app on my Windows Phone. I used a notebook to coordinate planning for my sister’s 21st birthday last year, and yes, I will be using OneNote to plan my wedding as well.

In conclusion, when I say that OneNote has changed my life, I am not exaggerating. Now to get the ultimate note-taking tool…

 

OneNote: The Continuation

I carried my OneNote knowledge into my current job. Nowadays, I use one big “Work” Notebook, which contains all of the projects I have worked on since starting with my company. It’s organised into section groups (one for each unit e.g. Asset Management, Water Engineering, Land Development, Environmental, Transport etc) containing separate sections for each individual project. Each section has an “Overview” page containing a brief description of the project and what my role on the project is, and underneath that are all the Outlook tasks, appointments and emails associated with that project.

I still keep detailed notes of the methodology I use for a task, and include screen clips using OneNote’s inbuilt screen clipping tool where necessary, especially for things like setting up an application, or how to connect to a server, or some random ArcMap tool settings. This approach has helped me save a lot of time, especially when repeating a similar task but on a different project.

One of the main reasons I bought a tablet was so that I could take notes on it. I use my Samsung Galaxy Note 8 in meetings to take minutes. I’ve used the pen as well now that handwriting support was added a few months ago, but my handwriting is atrocious since I’ve been OneNoting for almost 4 years now…

OneNote: The Beginning

I realised today, as I was typing up a post on Visual Studio, that I’ve never done a post on OneNote. I discovered OneNote in the first week of my working life. I had just started my GIS internship, and we were waiting for IT to grant us admin access to the computers so we could get on with the GIS. I was bored, so I was clicking around on the computer looking for things to do, when I noticed an application in the Microsoft Office group called OneNote 2010.

Clicking on that application that day changed my life. That is not an exaggeration. I’m pretty sure that I actually have 5 years working experience despite having only worked for 3 and a half years now because of OneNote. I’ve always loved studying, taking notes, and collecting as much knowledge as I can. That was actually one of my results from the Gallup StrengthFinder test.

With OneNote, I started clipping articles from the internet for research. Everytime I had a GIS question I couldn’t figure out, I would ask my mentor in an email, save her emailed answer in my Notebook, and add supporting information I found on the internet as subpages. Every Outlook task that I was assigned, I linked into OneNote so I would have a record of the task along with her detailed instructions. I created a “Notes” subpage under each task detailing the methodology I used to complete it.

I quickly learnt that if I had to change methodology, I shouldn’t delete the notes I had made – rather, I would use strikethrough to indicate that I had gone down the wrong path, and I had corrected it. This helped me greatly when I was on the verge of repeating previous mistakes. That didn’t really matter though, since I could always check out the previous page versions to see what I had done.

We would have regular “learning” meetings, where my mentor would cover certain topics. I would link the appointment from Outlook, and then type my written notes in OneNote once I was back at my desk. I also used it to make lists of things to do by using the tags.