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.

Nov 11, 2022 in Others 658 views

## 1 answer to this question.

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```

• 63,720 points

## SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I ran into the same problem due ...READ MORE

## can the font type of an Edittext,Radio Button and CheckBox be changed in Android

.I can able to change the font ...READ MORE

## can the font type of an Edittext,Radio Button and CheckBox be changed in Android

TextView text = (TextView) layout.findViewById(R.id.text); text.setText(msg); Typeface font = ...READ MORE

Try something along the lines of: for (i ...READ MORE

## Excel stock and sales data management

you must attach the event handler each ...READ MORE

## Using Excel VLOOKUP() function across two sheets

The syntax for VLOOKUP is VLOOKUP(Lookup_Value,Table Array,Col_index_num,Range_lookup) OR, to start in ...READ MORE

## Way to overcome Excel Vlookup function limit of 256 characters

If you are using VLOOKUP like this: =VLOOKUP(A2,D2:Z10,3,FALSE) i.e. ...READ MORE