excel_ref_functions: Excel Reference Functions

excel_ref_functionsR Documentation

Excel Reference Functions


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


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



One or more lookup values.


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


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


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


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.


Returns a vector the length of the input lookup values



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

# --- Basic Usage ---

        .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))

tidyquant documentation built on April 3, 2023, 5:13 p.m.