Yesterday we covered a lookup when all was well. Every item in the database had a link back to the table with the product names.
But sometimes you may not get hits on every lookup. Fortunately, Excel is pretty clear when there is not a match. Here is how to deal with a couple common errors.
One error often happens when something changes. To illustrate, let's suppose a new product line has been added, but we forgot to update the product name table.
Since there was no product line number 47 listed in the table, we got the "#N/A" error.
The fix is simple:
Is product line 47 a valid line? If not, then correct it.
If it is and it is missing from the product name table, then go to that table and add it.
Above we added line 47. Below we get a valid ink in our database.
In this case, there was not a match where there should have been. You may also face situations where there should not be a match. For example, perhaps you have a list of customers and want to pull in invoicing data. Some customers may not have bought during the period, so there should not be a match.
We created a situation like that with our example.
The customer in row 6 did not buy that day, so we do not expect a match. The formula comes back with an error. Here are the different ways to fix this situation:
Go to the cell with the error and just delete the formula. This works if you just have a couple errors
Sort the data and then erase the block of cells with the error. You could use this if you still want to keep the lockup formulas in the other cells.
If you are looking to clear the lookup formulas then you have a two step fix that is quick. This is great if you have a large database and several errors. First copy the column to itself so it converts to values (edit menu, paste special, values). The do a search and replace on the column, converting #N/A to blank, like we show below.
What if you have several errors in a large range but you want to save the lookup formula? Here is another fix. Keep the lookup column. Copy the column to the next column over as values. Then do a search and replace on the error in the new column. Finally rename and hide the column with the lookup formula.
Here is what it would look like before hiding the column.
The steps we took were:
Copying the lookup column to the next column as values
Doing a search and replace to clear out the #N/A values
Renaming the lookup column
Hiding the lookup column (not shown above)
Changing the named range for data to pick up the new column called Name
Here is an even better way- use this ISNA function to screen out the N/A errors- see this updated lesson- ISNA
Lookups are powerful. With the techniques shown here, you can still use lookups even when you don't have a match on all records. Remember too, to make sure you keep your tables up to date.