Google Sheets: INDEX, MATCH, VLOOKUP
Ever been faced with an issue where you can’t move your column to the far left in order to Vlookup? I just came across this issue recently.
The Problem – Vlookup
I needed to add teachers to a list of students who had to move into their class for two tests because their teacher (Hanna John-Kamen) would be absent on the test day.
Essentially, I was splitting the students into the remainder of the classes for that session time. In this student list sheet (Hannah 302-15) I had allocated the students new rooms and each room had a new teacher.
I couldn’t change the teacher proctors test list sheet ( Q2U1 Teacher) because the teachers who reference it would get confused. Unfortunately, the teacher’s name I wanted was in column B and the reference (The Speaking classroom) was in column I.
I need to put my first Vlookup in M2 of the Hannah 302-15 sheet. If I tried to Vlookup it might look like this: =VLOOKUP(L2,'Q2U1 Teacher'!B:I,-8, False). Where L2 is the class I am searching for in the Hannah 302-15 sheet. ‘Q2U1 Teacher’!B:I is the range where B is the name column and I is the Class number. –8 is clearly wrong. Vlookup will not accept a negative reference (It’s stupid, I know). False: because the list was not sorted.
So what to do.
The solution to Vlookup Left – INDEX and MATCH.
INDEX and MATCH are not exclusive to Google and can be found in LibreOffice, OpenOffice and MS Excel(Unless they put up a pay-wall for more exclusive formulas. Yes, I’m still mad about IFS. Damn it!!!)
Let’s break down INDEX and MATCH first so we know what we are dealing with.
INDEX
INDEX returns the contents of a cell or the range of a cell. It takes a range value which can be a single cell or a range. and then two optional values that identify the offset for the row and column.
=INDEX(cell or range, row offset, column offset)
For example
=INDEX(A2:C23, 1, 3)
This will display the value in the 1st row on the 3rd column in the range.
The offsets are kinda useless unless you have some tool to search through a range and provide its relative position.
Enter…
MATCH
MATCH searches through a range of cells looking for a value that you have indicated. When it finds the value, if provides you with the location of the value in the range.
It’s kinda like a search tool that provides a location of what you are searching for.
=MATCH(Search term, Range to search in, search type)
Search type is either (1) if the range is sorted in ascending order or (0) if it is not and an exact match is required. (0) is the go-to here unless you have a huge ordered data set.
For example:
=MATCH(“Cow”, A2:A17, 0)
Here I want to see where the Cow is on the range, A2:A17 (get it?) compared to the other farm animals. The Cow usually doesn’t put herself in alphabetical order, so we will search for an exact match with (0).
INDEX and MATCH
Combining Index and match then allows you to search for and item and bring up that item or something you want to offset it against, just like VLOOKUP offsets by column to the right. However, with the killer combo of INDEX and MATCH, you can offset by any amount. Cool, huh?… Well, I thought so.
Essentially our formula will look like this:
=INDEX(Place I want to find my answer, MATCH(What I am searching for, Where I am searching, Not ordered))
The MATCH formula finds the offset we need to get the right INDEX location.
Let’s head back to the example:
I want to add the teachers to the classroom they will proctor. I will search by the classroom and then find the teacher.
I put our first equation into cell M2 of the Hannah 302-15 sheet.
We will search and locate our teacher in the Q2U1 Teacher sheet. In this sheet, the place I want to find my answer is in the range of B9:B19 for all the teachers for that proctoring period. I want to make this an Absolute Reference to lock in the range so it will look like this: $B$9:$B$19
To find my answer, I will need to give my index an offset. I do this by using MATCH. I first reference the classroom back in the Hannah 302-15 sheet then provide a search location in the Q2U1 Teacher sheet at the range: $G$9:$G$19
Let’s plug the formula in:
=INDEX('Q2U1 Teacher'!$B$9:$B$19,MATCH(L2,'Q2U1 Teacher'!$G$9:$G$19,0))
‘Q2U1 Teacher’!$B$9:$B$19 – Points to the teachers.
L2 – Points to the Speaking Test Room in sheet Hannah 302-15 as our search key.
‘Q2U1 Teacher’!$G$9:$G$19 – Points to our Search index for the Speaking Test Room and provides the offset that we require to find the teacher.
The Result identifies Gemma Chan as the teacher proctoring that class.
Let’s drag it all the way down the Hannah 302-15 sheet to get the rest of the teachers.
sheet Hannah 302-15
As you can see, all the teachers are now added to the list. We VLOOKUP’d left (well kinda) using INDEX and MATCH.
It’s all looking a little bit messy, so let’s just give it a final sort before sending it to the teachers.
That’s it done and dusted. Time to send it off to my teachers to let them know the extra students they will have in their class.
~Yagi
Hello, an easier way to do (for me at least) is using ARRAYFORMULA.
Here’s and Example:
In “Sheet 1″:
=vlookup(J1:J,ARRAYFORMULA({‘Accounts Payable’!J:J,’Accounts Payable’!A:A}),2,0),””))
With this you can take advantage of other formulas.
Example:
=ARRAYFORMULA(iferror(vlookup(J1:J,ARRAYFORMULA({‘Accounts Payable’!J:J,’Accounts Payable’!A:A}),2,0),””))