Using Lookups (Vlookup and Hlookup)

• VLOOKUP (lookup_value,table_array,col_index_num,range_lookup) - to lookup something across columns
• HLOOKUP (lookup_value,table_array,row_index_num,range_lookup); - to lookup something down rows

Let's take an example - if you had a spreadsheet with an ID, Student Name, Course, Major and Exam Score

Screenshot 1:

 (CURRENT SHEET)

Let us also assume there is a separate tab/or spreadsheet called 'Grading Schedule' which has two columns containing marks range (sorting does not matter, they can be in any order)

Screenshot 2:

(GRADING SCHEDULE)

Now to our own spreadsheet, let us add another new column,Grade, which will contain grades secured by the students. The idea is that rather than manually look at the marks scored and see what grade would be obtained, we are able to "lookup" the grade in the Grading Schedule table and obtain an answer. 

Now under grade Grade first row, we will write a VLOOKUP function. The syntax of this function is
      VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup] )

You can then drag this formula down all your rows to apply it to all the exam scores and find the equivalent grade from the above table. 

Screenshot 3:

 (CURRENT SHEET)

• The first parameter of the formula lookup_value defines the value of the number which we will be looking up in the 'Grading Schedule' table.
  (In this example, we will be looking up 90, from the Exam Score field)
   
• The next parameter, table_array defines the table we will be referring to. 
  (In this example, as we saw above as well, the table we will be looking this data up in is the 'Grading Schedule'  containing ranges of marks and grades in the range A1: B8, the cells where there is data we would need) Note: Column A needs to be the column of the values we want to pull into the other sheet.
• col_index_num defines data from which column we want to extract corresponding values.  
  (In this case, it would be the second column that contains grades)
   
• [range_lookup] lets you to choose an option  either TRUE or 1(approximately matching of values) or FALSE or 0 (Exact matching of values).
  (In this case, we want values in a range, not exact but those that are upto a certain point, hence we choose the TRUE option)
  
  

Now let us consider that we want to see how these 5 students grades compare to their exam scores and grades last semester and this data is stored in a separate spreadsheet, that looks like this: 

Screenshot 4:
 (LAST SEMESTER GRADES SHEET)

We don't want to manually check the grades for each student - so the trick here is to use a lookup using some unique identifier, say ID. We go back to our Current Grades sheet and add in a column called "Previous Score". 

We have now managed to pull in the previous scores for these students using their IDs to perform the lookup.

NOTE: The reason here the [range_lookup] is set to 0 or  FALSE, is because we need the ID to be an EXACT match, not an approximate match as in the case of the grades above

Now that we have the previous scores, if we want to compare the grades, we can once again pull in in the respective grades that correspond to the 'Grading schedule using a similar lookup as in Screenshots 2 and 3 . 

 (CURRENT SHEET)

Now it would be easy to even visually compare past and current performance, or even perhaps, write and IF statement to compare Better or Worse Performance. If at a later stage suppose you wanted to incorporate each student's home address and phone number from an Admissions spreadsheet, you could just use their ID to look up the details. 

And that's it! As easy as that!

Things to remember with respect to lookups:

1. Use with large amounts of data, spread across several sheets, workbooks to easily compare and lookup corresponding values.
2. If a value does not exist in the lookup table, say looking for ID 100 in the table - you will get a "#N/A" error. This is also helpful as you can often use this to see what is missing from a table. For example, I use this to make sure all phone numbers in a list match our list of usernames. Anything that popped up an error, indicated to me that there was potentially a mistake in the phone number and I could investigate it further. 
3. You MUST use unique identifiers. If you choose to look up say, by the Student name, and there are two Johns - the lookup will always return the value for the first instance of John that shows up in the lookup table. If you want you can always create a unique identifier using CONCATENATE - for example: John_Mathematics_Calculus I and then perform the corresponding lookup.