Troubleshooting errors in VLOOKUP

Excel - Functions and Formulas

If the lookup_value is not found in the leftmost column, VLOOKUP returns a #N/A error. There are simple ways to either prevent or handle this error.

The best method is to use the Data Validation command to give the user a list of allowable entries. To create a Data Validation list,
1. Select the lookup_value cell.
2. On the Data ribbon, in the Data Tools group, select Data Validation, and then the Data Validation item.
3. In the Allow box select List so a drop-down list is used to present allowed choices.
4. In the Source box select the leftmost column of the table containing the allowed entries.
5. Choose Ok.

Use a Data Validation list to allow only valid entries in a VLOOKUP.

Use a Data Validation list to allow only valid entries in a VLOOKUP.

Now, when someone clicks in D4 to enter the Salesrep, they will be presented with a drop-down list of only valid Salesreps. Notice that in the Data Validation dialog box you also can enter error messages in the Input Message tab or display an alert you select from the Error Alert tab.

Another less elegant way to handle this problem is to allow the user to type in any entry in the cell. Instead of entering just a VLOOKUP function you enclose it in an IFERROR function that displays an error message when an incorrect entry is entered. In the following formula that could be used in D7 the error message “This is not a valid entry.” will display instead of a #N/A error.

=IFERROR(VLOOKUP($D$4,$C$12:$F$18,4,FALSE),”This not a valid entry.”)

Share the power...