Can R do the equivalent of an HLOOKUP nested within a VLOOKUP

Question

I'm using R Studio to attempt—ineffectively—to perform the Excel equivalent of an HLOOKUP nested within a VLOOKUP.

The scenario is as follows.

I've got two tables. Historical stock prices are shown in Table 1, where each row corresponds to a specific date and each column to a ticker name. The closing stock price for each ticker on each date is shown in Table 1.

Let's say Table 1 appears as follows:

 |----------------------------|
 |   Date   |MSFT | AMZN |EPD | 
 |----------------------------|
 | 6/1/2020 | 196 | 2600 | 19 |
 | 5/1/2020 | 186 | 2200 | 20 |
 | 4/1/2020 | 176 | 2000 | 15 |
 | 3/1/2020 | 166 | 1800 | 14 |
 | 2/1/2020 | 170 | 2200 | 18 |
 | 1/1/2020 | 180 | 2300 | 17 |
 |----------------------------|

A set of ticker symbols, two dates, and placeholders for the stock price on each date are shown in Table 2. Each of Date1 and Date2 corresponds to a date in Table 1, and Date1 is always earlier than Date2. In Table 2, you'll see that Date1 and Date2 are distinct for each row.

In Table 2, I want to insert the relevant PriceOnDate1 and PriceOnDate2 data in a manner similar to how Excel's VLOOKUP and HLOOKUP functions work. (I am no longer able to use Excel for this because the file is too large for Excel to handle.) Then, using a formula like this, I can determine the return for each row: Date2 divided by Date1

Assume I want Table 2 to look like this, but I am unable to pull in the pricing data for PriceOnDate1 and PriceOnDate2:

|-----------------------------------------------------------|
| Ticker | Date1    | Date2    |PriceOnDate1 |PriceOnDate2  |
|-----------------------------------------------------------|
| MSFT   | 1/1/2020 | 4/1/2020 | _________   | ________     | 
| MSFT   | 2/1/2020 | 6/1/2020 | _________   | ________     |   
| AMZN   | 5/1/2020 | 6/1/2020 | _________   | ________     |
| EPD    | 1/1/2020 | 3/1/2020 | _________   | ________     |   
| EPD    | 1/1/2020 | 4/1/2020 | _________   | ________     |
|-----------------------------------------------------------|

My question is whether there is a way to use R to pull into Table 2 the closing price data from Table 1 for each Date1 and Date2 in each row of Table 2. For instance, in the first row of Table 2, ideally, the R code would pull in 180 for PriceOnDate1 and 176 for PriceOnDate2.

Answer 1

When working in a program like R, you must adopt a slightly different perspective on the data. Your Table 1's lengthy format makes it perhaps the easiest to work with. The desired values can then be pulled by simply joining the Ticker and Date together.

Data:

table_1 <- data.frame(Date = c("6/1/2020", "5/1/2020", "4/1/2020", "3/1/2020", 
                               "2/1/2020", "1/1/2020"),
                      MSFT = c(196, 186, 176, 166, 170, 180),
                      AMZN = c(2600, 2200, 2000, 1800, 2200, 2300),
                      EPD = c(19, 20, 15, 14, 18, 17))

# only created part of Table 2
table_2 <- data.frame(Ticker = c("MSFT", "AMZN"),
                      Date1 = c("1/1/2020", "5/1/2020"),
                      Date2 = c("4/1/2020", "6/1/2020"))

Solution:

The tidyverse approach is pretty easy here.

library(dplyr)
library(tidyr)

First, pivot Table 1 to be longer.

table_1_long <- table_1 %>% 
  pivot_longer(-Date, names_to = "Ticker", values_to = "Price")

Then join in the prices that you want by matching the Date and Ticker.

table_2 %>% 
  left_join(table_1_long, by = c(Date1 = "Date", "Ticker")) %>% 
  left_join(table_1_long, by = c(Date2 = "Date", "Ticker")) %>% 
  rename(PriceOnDate1 = Price.x,
         PriceOnDate2 = Price.y)

#   Ticker    Date1    Date2 PriceOnDate1 PriceOnDate2
# 1   MSFT 1/1/2020 4/1/2020          180          176
# 2   AMZN 5/1/2020 6/1/2020         2200         2600