This article examines the Microsoft Excel VLOOKUP as an alternative to using nested “Ifs”. The Excel VLOOKUP function is one of the most asked about Excel functions. There are more Google searches for Excel VLOOKUP than any other function so it really is worth using.
The IF function is used where there are two or more possible cell entries. For example, it could be used to calculate the appropriate bonus for a list of sales staff.
Suppose we have a table of sales figures, and are going to award a bonus of 5% of total sales for anyone whose sales are at least £10,000 for the month:
Where a set of formulae are each referencing a single value, it is good practice to store the value in a cell, and then refer to it as either an absolute cell reference or a range name. These will not change when the formula is copied. Hence, if the value needs to change then only one cell needs editing.
In the example above, the cells containing the bonus rate and quota have both been named, “Bonus_rate” and “Quota” respectively. A quick way to do this is to make use of the labels in E1 and E2. Select the range E1:F2 then choose Formulas>Defined Names>Create from Selection:
Clicking OK will name cell F1 “Bonus_rate” and cell F2 “Quota”. Then enter he following formula in cell C2:
Copy the formula down the column. The range names act like absolute cell addresses and do not change when copied, giving the following results:
The text of the formulae is:
If there are more than two possibilities, then one solution is to use nested IFs. Suppose if the sales are less than £5,000 no bonus is paid; for sales of at least £5,000 but less than £10,000 the bonus rate is 2%; for sales of £10,000 or more 5% is paid. We could insert this formula in B2:
If the number of possibilities is more than three, then further nestings to a maximum of 64 levels may be added.
A problem with this is that such formulae rapidly become overly complex. Also, adding further possibilities involves editing the formulae.
A useful alternative is to use the VLOOKUP or LOOKUP functions.
The basic syntax of Excel VLOOKUP is:
The optional fourth argument is either TRUE or FALSE, with TRUE as the default. If this is the case, then the data in the range specified by the table_array should be sorted in ascending order of its first column. Also, if the lookup_value is not found, then Excel will do an approximate lookup, returning the value for the previous row.
For example, consider a table listing grades corresponding to different percentage scores in a test. The pass mark is 50%, with scores of at least 50% but less than 60% gaining a C; at least 60% but less than 70% B; and 70% or more an A.
The following table may be used:
Consider an Excel VLOOKUP function where the lookup_value is the student’s score, the table_array is the range A2:B5 and the column_index_number is 2. If the score is exactly 50% then “C” would be returned, similarly for other scores with an exact match. However, if the score were (say) 58% and the VLOOKUP function were performing an approximate range lookup, then it would return the value for the 50% score, ie “C”; for 62% it would be “B”, and so on. This is because if there is not an exact match Excel does not round up the lookup value as might be expected; it always rounds down to the previous value.
Some further examples illustrate that a Excel VLOOKUP function applied to a table like this is an alternative to nested IFs. A score of 49%, although closer to 50% than 0% would return “F”; 70% or more would return “A”.
Where the table_array has two columns and this approximate range_lookup is required then an simpler alternative is to use the LOOKUP function. This has two versions, with the simpler one having just two arguments:
The table_array may be a named range. Applied to the above problem, the following is a better solution:
The range E1:F4 has been named “Bonus_table”. One way to do this is, having selected the range, choose Formulas>Define Name and enter the required name:
The formula in cell C2 is changed to:
When writing a formula, if you select a range that has been named, then Excel will insert the range name in the formula. Another method is, while writing the formula, to choose Formulas>Defined Names>Use in formula and select the required range name.
Not only is this formula simpler than the original nested IF’s but it is also easier to update. Suppose we decide that sales of at least £7,500 but less than £10,000 earn a 3% bonus. All that is needed is to add a further row to the bonus table by selecting cells E4:F4, pressing the right mouse button and selecting Shift cells down:
A new row will be added and the appropriate values can be inserted. You should find that the area referenced by Bonus_Table has been automatically expanded to include this. (Click the drop-down arrow by the name box and choose the range name):
If extra rows are added outside of the area of the named range, then you will need to redefine it using the Name Manager. We will say that sales of £15,000 or more will earn a 10% bonus. Add the extra row at the base of the Bonus Table, then select Formulas>Defined Names>Name Manager. Change the area referenced by Bonus_Table to be E2:F6.
Click the tick below “Refers To” and close the dialogue box. Observe the change in the values returned for John Smith and Jane Doe:
Neither of these changes has required any editing of the formulae; all that is necessary is changing the area referenced by the named range. Also, the limit imposed by 64 nesting levels does not apply; we could have a million or more possibilities given the number of available worksheet rows.
You can learn about the Microsoft Excel VLOOKUP function on our Excel training courses.
Excel VLOOKUP references
15 April 2016