3. Prevent #N/A Errors in VLOOKUP
If you want to prevent any #N/A errors in your VLOOKUP, you would have to use a series of functions:
-
=IFERROR() will return TRUE if the result of the VLOOKUP is an error. You could also use =ISNA().
-
=IF() can be used to test if the ISERROR() returns True. If an error is returned, then specify alternate text to be used, perhaps “Unknown Rep.” If an error is not returned, then you have Excel put in the results of the VLOOKUP.
The following formula will replace the #N/A errors with alternate text.
=IF(ISERROR(VLOOKUP(C2,RepTable,2,FALSE)),"Unknown Rep",VLOOKUP(C2,RepTable,2,FALSE))
Tip: In Excel 2007, the new =IfError() function simplifies this formula:
=IFERROR(VLOOKUP(C2,RepTable,2,FALSE),"Unknown Rep")
6. Convert your lookup values to be the same type and format as the lookup table.
If none of the VLOOKUPs are working (all are returning a #N/A), it is possible that either the lookup value or the first column of the lookup table are in the wrong format. In this case, the values in column C are numeric and the values in column J are text.
Use a function to transform C2 to the proper format and the VLOOKUPs will work:
In this case the =TEXT() function converted numeric values to text with three digits. Some other useful functions for transforming data:
=UPPER() will convert text to upper case
=LOWER() will convert text to lower case
=PROPER() will convert text to proper case
=TRIM() will remove leading and trailing spaces from text
=VALUE() will convert numbers stored as text to values.
Dostları ilə paylaş: |
