excel_ref_functions {tidyquant}

Excel Reference Functions

Description

Excel reference functions are used to efficiently lookup values from a data source. The most popular lookup function is "VLOOKUP", which has been implemented in R.

These functions are designed to help users coming from an Excel background. Most functions replicate the behavior of Excel:

• Names are similar to Excel function names

• Functionality replicates Excel

Usage

VLOOKUP(.lookup_values, .data, .lookup_column, .return_column)

Arguments

.lookup_values

One or more lookup values.

.data

A data.frame or tibble that contains values to evaluate and return

.lookup_column

The column in .data containing exact matching values of the .lookup_values

.return_column

The column in .data containing the values to return if a match is found

Details

VLOOKUP() Details

• Performs exact matching only. Fuzzy matching is not implemented.

• Can only return values from one column only. Use dplyr::left_join() to perform table joining.

Value

Returns a vector the length of the input lookup values

Examples

library(tidyquant)
library(tidyverse)

lookup_table <- tibble(
    stock   = c("FB", "AMZN", "NFLX", "GOOG"),
    company = c("Facebook", "Amazon", "Netflix", "Google")
)

# --- Basic Usage ---

VLOOKUP("NFLX",
        .data = lookup_table,
        .lookup_column = stock,
        .return_column = company)

# --- Usage with tidyverse ---

# Add company names to the stock data
FANG %>%
    mutate(company = VLOOKUP(symbol, lookup_table, stock, company))