[MUSIC] We are now going to experiment with one of the issues you'll encounter when using a VLOOKUP. Sometimes, the data you are trying to look up does not exist within your data set. And therefore the function returns an error message. We have an example of this in the table above, which is looking up information about specific distributors from the sales data, some of which exist in our data set, and others that don't. You'll see that there is no distributor with an ID of 3000 or 4000. So, Excel is returning an error message. We see this quite frequently in business. Since a product may not have been sold during a specific period or the specific variable set we are working with does not apply to all the situations. It also happens when data cleansing has not been performed correctly. The error message does not only look poor. It is also not helpful in explaining what the issue is and it could cause further downstream issues when you're trying to run computations on the dataset. For example, if I do =sum on a range containing even a single error message, the sum function will return an error. However, Excel has given us a tool to work around this. It's called the IFERROR function. The IFERROR function tells Excel what value you would like to return if an error occurs. So let's try rebuilding the table above using the IFERROR function. We're going to start with cell C107 and type the formula we want to start working with. Since Excel works with the order of operations PEMDAS, the IFERROR should be enacted last. I'm going to start with my IFERROR statement as the outermost argument to follow this logic. So let's type =IFERROR. As you can see, the syntax is comprised of two parts. The first part being what argument or series or arguments does the statement apply to? And what do you want Excel to do if it encounters an error at that statement? The value we care about here is the output from the VLOOKUP. Let's start by typing the view lookup formula we have been working with. The view lookup value is the distributor ID in B107. We know that we want to anchor B107 so that we can use it again for other cells. The table array if we remember from earlier is the sales data, which is a named range. Open the Name Manager using F3, and double-click on this cell's data. Now, remember from previous exercises that the name is in column 2, and we don't want an approximate match, so we are going to type false. When I close the parenthesis, you'll notice that I'm no longer working in the VLOOKUP statement. I'm back to working in the IFERROR statement. When I hit comma, I shift from the value field to the value that Excel will display in case my view lookup formula results in an error. I would like it to say data not available. Know that since this is text, I have to add quotes to each side of my text string. I'm going to insert a quotation mark and then type, Data Not Available, close the quotes. Close the second formula that I'm working with and click Enter. The name comes up because there's a match. However, we know there is nothing for the second distributor. So let's see what happens when we paste it down. Distributor ID 4000 comes back with Data Not Available as we would expect. Now since we properly locked this information, we can use this formula to fill in revenue as well. Let's work with the revenue and copy and paste a formula over. It's coming back with the name because we still need to update the column number. We remember from earlier that the revenue can be found in column 10, so I'm going to change my 2 to 10. For the error statement here, I'm going to change the error statement from Data Not Available to the number 0 as we are working with a numerical output. To accomplish this, I will exchange the error statement to a 0 and remove the quotes as we are no longer dealing with a text input. You will notice that we have revenue values for everywhere there's a match and where the data is not available, the output is 0. That now makes it possible to run math off our new revenue column. For example, we can even sum the revenue column and find that the total revenue for these distributors is $184.86. Now it's your turn to practice problem 3b on your own. Work up the average unit price for the product, and then use an average formula to calculate the average unit price across all the distributors found in column B. [MUSIC]
