How to Perform a VLOOKUP (Similar to Excel) in R

The VLOOKUP function in Excel allows you to look up a value in a table by matching on a column.

For example, in the following Excel worksheet we can look up a player’s team name by using the VLOOKUP to match on player name and return the player’s team:

We can replicate this function using base R or the dplyr package:

Using Base R:

merge(df1, df2, by="merge_column")

Using dplyr:

inner_join(df1, df2, by="merge_column")

The following examples show how to use each of these functions in R to replicate the VLOOKUP function from Excel.

VLOOKUP Using Base R

The following code shows how to perform a function similar to VLOOKUP in base R by using the merge() function:

#create first data frame
df1 LETTERS[1:15],
                  team=rep(c('Mavs', 'Lakers', 'Rockets'), each=5))

#create second data frame 
df2 LETTERS[1:15],
                  points=c(14, 15, 15, 16, 8, 9, 16, 27, 30, 24, 14, 19, 8, 6, 5))

#merge the two data frames
merge(df1, df2, by="player")

   player    team points
1       A    Mavs     14
2       B    Mavs     15
3       C    Mavs     15
4       D    Mavs     16
5       E    Mavs      8
6       F  Lakers      9
7       G  Lakers     16
8       H  Lakers     27
9       I  Lakers     30
10      J  Lakers     24
11      K Rockets     14
12      L Rockets     19
13      M Rockets      8
14      N Rockets      6
15      O Rockets      5

Notice that this returns the same results as the VLOOKUP function from the introductory example. Also note that you can specify multiple columns to merge on using the by argument.

VLOOKUP Using dplyr

library(dplyr)

#create first data frame
df1 LETTERS[1:15],
                  team=rep(c('Mavs', 'Lakers', 'Rockets'), each=5))

#create second data frame 
df2 LETTERS[1:15],
                  points=c(14, 15, 15, 16, 8, 9, 16, 27, 30, 24, 14, 19, 8, 6, 5))

#merge the two data frames using inner_join
inner_join(df1, df2, by="player")

   player    team points
1       A    Mavs     14
2       B    Mavs     15
3       C    Mavs     15
4       D    Mavs     16
5       E    Mavs      8
6       F  Lakers      9
7       G  Lakers     16
8       H  Lakers     27
9       I  Lakers     30
10      J  Lakers     24
11      K Rockets     14
12      L Rockets     19
13      M Rockets      8
14      N Rockets      6
15      O Rockets      5

Notice that this returns the same results as the VLOOKUP function in Excel. Also note that you can specify multiple columns to merge on using the by argument.

Also, if you’d like non-matches to be shown you can instead use the left_join function.

Additional Resources

How to Calculate Cumulative Sums in R
How to Standardize Data in R
How to Append Rows to a Data Frame in R