Table of Contents
A VLOOKUP, or Vertical Lookup, is a function in R that allows users to search for specific data in a table or range and then return a corresponding value or result from the same row. It is similar to the VLOOKUP function in Excel and is an important tool for data analysis. To use it, you must specify the data range, the column that contains the search value, and the column that contains the return value. The last two arguments are optional and provide additional control over the search criteria and the returned value. With these arguments, you can create powerful and efficient data analyses.
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 <- data.frame(player=LETTERS[1:15], team=rep(c('Mavs', 'Lakers', 'Rockets'), each=5)) #create second data frame df2 <- data.frame(player=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 <- data.frame(player=LETTERS[1:15], team=rep(c('Mavs', 'Lakers', 'Rockets'), each=5)) #create second data frame df2 <- data.frame(player=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.
How to Calculate Cumulative Sums in R
How to Standardize Data in R
How to Append Rows to a Data Frame in R