IFERROR

Imagine you run a tutoring service at your local college for math. Considering that your business has been growing recently, you have been receiving more applications for students to become tutors.

Each student that applies must give their name, year, and most advanced math class that they've taken.

Your base pay for tutors is $15/hour. However, the more advanced the tutor is in math, the more you pay them. Here is the table of your pay rate increases dependent on the most advanced math class the student has taken:

While most students have taken one of these advanced math classes, not all of them have. Any student who has not taken one of them will be paid the base rate of $15/hour.

Based on our table of pay rate increases, we can use an IFERROR function to determine what the pay rate increase each applicant will receive. If no pay rate increase is applicable, then we'll give them the base amount of $15/hour.

IFERROR is used when you have a function that may return an error. It enables you to set a backup value for the cell to be set to in case the function returns an error.

You might be wondering, when would this tutoring business situation have a function return an error? And what would the backup value be in those cases?

Consider this: we'll be using a VLOOKUP function on the pay rate increase table...

...to determine the pay rate increase for each applicant based on their most advanced math class. However, not all students have taken math classes in the pay rate increase table. Therefore, the VLOOKUP for those students will return an error.

When this error is returned, we want to give that applicant the base rate of $15/hour.

Coding an IFERROR

We're actually going to start by coding our VLOOKUP function to determine the pay rate increase for our first applicant, Arnie.

Below is the template for the VLOOKUP function. Let's go through each argument and determine what we should code.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Our lookup_value is Arnie's class, which is stored in cell C2.

=VLOOKUP(C2, table_array, col_index_num, [range_lookup])

The table_array is the "Pay Rate Increase" table, which is contained in cells F3:G7.

=VLOOKUP(C2, F3:G7, col_index_num, [range_lookup])

The col_index_num is going to be the pay rate corresponding to the class, which is stored in the 2nd column of the "Pay Rate Increase" table.

=VLOOKUP(C2, F3:G7, 2, [range_lookup])

Lastly, for [range_lookup] we're going to set it to FALSE, because we want an exact match of class.

=VLOOKUP(C2, F3:G7, 2, FALSE)

When we plug this into D2, notice how we get an error:

Why?

Because Arnie's most advanced math class does not occur in the "Pay Rate Increase" table, and therefore she'll receive the base rate of $15/hour.

Herein lies the purpose of the IFERROR function: some of these applicants won't be eligible for the pay increase. Those that aren't should be given the base rate of $15/hour.

Now that we've visualized the purpose of the IFERROR function, let's incorporate it into Arnie's pay rate calculation in cell D2. Here is the template for the IFERROR function to get us started:

=IFERROR(value, value_if_error)

The first question to ask here is: what should our value be for this IFERROR function?

It's actually just the VLOOKUP function from before. Ideally, this is the value that we want displayed in each cell.

=IFERROR(VLOOKUP(C2, F3:G7, 2, FALSE), value_if_error)

However, not all cells will return a value from this function, as we experienced with Arnie. It's in these cases that we need to utilize the value_if_error and display the base rate of $15/hour.

=IFERROR(VLOOKUP(C2, F3:G7, 2, FALSE), "$15/hour")

When we plug this into D2, notice how instead of getting an error for Arnie, we now get $15/hour:

Before we copy this down through the rest of the rows in the table of applicants, we need to absolutely reference the "Pay Rate Increase" table in cells F3:G7. That way, it doesn't shift cell reference as we copy the formula down.

=IFERROR(VLOOKUP(C2, $F$3:$G$7, 2, FALSE), "$15/hour")

Once we modify this in cell D2, we can copy the formula down like so:

And there you have it! We've successfully used the IFERROR function to determine each applicant's pay rate. All applicants who had taken one of the advanced math classes in the "Pay Rate Increase" table got assigned that pay rate increase. Any applicants that didn't take one of the advanced math classes (and therefore, had their VLOOKUP function return an error) got assigned the base rate of $15/hour.