*49*

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