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

VLOOKUP is a function in excel and it is an acronym for vertical lookup. The task of this function is to search for a particular value in a column to return a value from a different column but in the same row.

Syntax:

VLOOKUP([value], [range], [column no], [true/false])

Here,

• value: Specifies the value to be searched
• range: It specifies the range in which the value has to be searched
• column no: The number of the column that contains the return value
• true: If the user wants approximate match
• false:  If the user wants the exact match with the specified value

Method 1: Using Base R To Perform VLOOKUP:

We can achieve VLOOKUP in base R using the merge() function.

Syntax:

merge(dataFrame1, dataFrame2, by = “columnName”)

Here,

• dataFrame1 and dataFrame2 are the dataFrames and by argument is optional and used to specify multiple columns to merge

Example:

In this program, firstly, we have created two dataframes. Then we have applied the merge function. Note that we have merged columns on the basis of section column which is the same in both the dataframes.

R

# R program to perform VLOOKUP
# using merge function

# creating a dataframe
dataFrame1 < - data.frame(section=LETTERS[1:15],
                          team=rep(c('Alpha', 'Beta', 'Gamma'),
                                   each=5))

# creating another dataframe
dataFrame2 < - data.frame(section=LETTERS[1:15],
                          score=c(25, 13, 12, 16, 18, 19,
                                  26, 28, 20, 36, 44, 29,
                                  8, 6, 5))

# merge the two dataframes
merge(dataFrame1, dataFrame2, by="section")

Output:

Method 2: Using dplyr To Perform VLOOKUP

We can use the inner join function of the dplyr library in R to perform similar to the VLOOKUP function.

Syntax:

inner_join(dataFrame1, dataFrame2, by=”columnName”)

Here,

• dataFrame1 and dataFrame2 are the dataFrames and by argument is optional and used to specify multiple columns to merge

Syntax to install and import the dplyr package:

install.package('dplyr')
library(dplyr)

Example:

In this program, firstly, we have created two dataframes. Then we have applied the inner_join function. Note that we have merged columns on the basis of section column which is the same in both the dataframes.

R

# R program to perform VLOOKUP
# using dplyr

# Including library
library(dplyr)

# creating a dataframe
dataFrame1 <- data.frame(section=LETTERS[1:15],
                  team=rep(c('Alpha', 'Beta', 'Gamma'),
                           each=5))

# creating another dataframe
dataFrame2 <- data.frame(section=LETTERS[1:15],
                  score=c(25, 13, 12, 16, 18, 19,
                          26, 28, 20, 36, 44, 29,
                          8, 6, 5))

# merging the two dataframes by using
# inner_join function
inner_join(dataFrame1, dataFrame2, by="section")

Output: