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

• Source: The Wall Street Journal - http://quotes.wsj.com/AU/XASX/BOQPD/historical-prices Accessed 27 August 2016
• Issuer: Bank of Queensland
• Instrument type: Convertible preference shares (CPS)
• Dividends: semi-annually in arrears, at bank bill swap rate (BBSW) plus 5.10% per annum

The workbook

• Worksheet: Summary, Range: Dates (workbook scope), 30Jun16 to 29Jul16 in descending order. Rule of 2029 applied
• Worksheet: BOQPD.AX, Range: BOQPD (worksheet scope)
• Worksheet: MissingData, Range: BOQPD_ (workbook scope)
• Defined names: DateRef, Refers to: =Summary!RC1

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.

• This example was developed in Excel 2013 with VBA 7.1
• Revised: Tuesday 3rd of January 2017 - 07:28 PM, [Australian Eastern Time (AET)]