When you have very large Nested IF Functions, you may consider using the VLOOKUP or HLOOKUP Functions, instead.
With a VLOOKUP Function, you can enter the data (like our grading scale) into a separate worksheet list and have Excel refer to the list when searching for a value to return. This way, you don’t have to type it all into a long, complicated IF Function. VLOOKUP stands for Vertical Lookup. You use a Vertical Lookup (or, VLOOKUP) when your lookup list contains your data in columns.
VLOOKUP and HLOOKUP are very similar functions; however, they differ in how they search your lookup list. So, we’ll talk about VLOOKUP first and then, in the next lesson, cover HLOOKUP.
The VLOOKUP Function searches for a value in the first column of a lookup list and returns a value in the same row from another column in the table array.
The sample above reflects a VLOOKUP Function. Now, let’s break this down:
VLOOKUP = Name of the Function
Lookup_value = What value are you looking for in the 1st column of your list
Table_array = This is the range of your lookup list
Col_index_num = Of the columns in your list, which column contains the data you want the formula to display?
Range_lookup = Either True or False (TRUE = Close Match / FALSE = Exact Match)
Now, let’s put that into action using our grading scale example. I’d like Excel to look at a student’s grade. And, based on the score they’ve received, I’d like Excel to display the corresponding letter grade from the lookup list.
Here’s what my formula would look like in Excel: