IFERROR | CSE 148 – Excel Exam 2

Still feeling nervous for your exam? Check out the Excel Cram Kit (Exam 2) for practice modules that emulate the difficulty that you'll face on your exam. Each module includes a thorough step-by-step explanation where I walk you through the entire problem solving process!

IFERROR explained

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 advancedmath 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 advancedmath 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_valuetable_arraycol_index_num, [range_lookup])

We'll get to that in a little bit. I first want you to see how this function will return an error, and how the IFERROR function will fix those errors.

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

=VLOOKUP(C2table_arraycol_index_num, [range_lookup])

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

=VLOOKUP(C2, F3:G7col_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.

Practice problem

You run a Midwestern-based e-commerce business and have a table of your transactions for the month:

Given the above list of transactions and what states they occured in, determine the state tax rate applied based on the table on the right.

Some transactions occurred in states that are outside your typical Midwestern territory. In these cases, display "UNKNOWN" for the state tax rate applied.

=IFERROR(VLOOKUP(C2, $F$2:$G$5, 2, FALSE), "UNKNOWN")

The above formula should be copied through all cells between D2:D11.

Excel (Exam 2) Follow-along Guide

It's no secret you retain info better when you write it down. That's why I created the Excel (Exam 2) Follow-along Guide for you!

As you come upon key concepts highlighted in yellow, like this sentence here, you can fill-in-the-blanks on your Follow-along Guide so that you remember all the important stuff for later!

You can obtain the Excel (Exam 2) Follow-along Guide for FREE by entering your email below!

Next, let's learn how to use the nested IF and IFS functions to write conditionals with multiple conditions.

Follow-along Guide

Fill-in-the-blanks for highlighted, key points in the concepts below!

Free Resources

Each exam concept broken down in simple, real-world examples!

ToolsExcel (Exam 2) Follow-along Guide
LessonAND vs. OR
LessonHLOOKUP
LessonIFERROR
LessonNested IF vs. IFS
LessonExcel tables
LessonPivotTables
LessonCOUNTIF, SUMIF, AVERAGEIF
LessonSUMPRODUCT
Lesson3D Cell References

Practice Problems

Step-by-step walkthrough for each of the questions you need to be ready for!

"I got this review for my 2nd CSE exam after not doing so well on the first one, and it was so helpful! Will definitely be using again in the future."

Erin Graham
Oct. 26, 2021

Rating: 5 out of 5.

Leave a Comment