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):
- 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
- 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
- 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: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Time (AET)]