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.
Continue reading “VLOOKUP Left in Google Sheets with INDEX and MATCH”