VLOOKUP with #N/A errors

VLOOKUP primary and secondary arrays

This module demonstrates VLOOKUP procedures linking a summary worksheet to a primary data array worksheet, and a secondary data array worksheet. The main point is to handle the #N/A error, if the data (lookup value) is not in the primary data worksheet.


The data


The workbook


VLOOKUP #N/A error handling


Three examples are shown (#N/A means that the value is not available):

  1. IF(ISNA(… The ISNA function has been around since the time of Lotus 1-2-3. ISNA is designed to handle the #N/A error. If ISNA returns TRUE then VLOOKUP uses the secondary array
  2. IF(NOT(ISNA … Some analysts may prefer to test for no #N/A error with the NOT unary operator. If NOT(ISNA returns TRUE then VLOOKUP uses the primary array
  3. IFNA(… - new in Excel 2013. This function combines the IF(NOT(ISNA … nested sequence

IF(ISNA(...


=IF(
ISNA(VLOOKUP(DateRef,BOQPD.AX!BOQPD,COLUMN(),FALSE)),
VLOOKUP(DateRef,BOQPD_,COLUMN(),FALSE), << Secondary array
VLOOKUP(DateRef,BOQPD.AX!BOQPD,COLUMN(),FALSE) << Primary array
)


IF(NOT(ISNA(...


=IF(
NOT(ISNA(VLOOKUP(DateRef,BOQPD.AX!BOQPD,COLUMN(),FALSE))),
VLOOKUP(DateRef,BOQPD.AX!BOQPD,COLUMN(),FALSE), << Primary array
VLOOKUP(DateRef,BOQPD_,COLUMN(),FALSE) << Secondary array
)


IFNA(...


=IFNA(
VLOOKUP(DateRef,BOQPD.AX!BOQPD,COLUMN(),FALSE), << Primary array
VLOOKUP(DateRef,BOQPD_,COLUMN(),FALSE) << Secondary array
)


Selected Excel functions used in this module.


Excel functions Description
IFNA(value, value_if_na)Returns the specified value if a #N/A error occurs, otherwise returns the result of the formula
ISNA(value)Returns TRUE if value is a #N/A error
NOT(logical)Returns the opposite logical value


The three examples are demonstrated in figure 1, Excel web app #1.


Fig 1: Excel Web App #1 - the VLOOKUP primary and secondary array example