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

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

by

The VLOOKUP function in Excel helps you to look up a value in a table by matching on a column. VLOOKUP is an Excel function that stands for vertical lookup.

This function’s job is to look for a specific value in a column and return a value from a different column but in the same row.

Syntax:

VLOOKUP([value], [range], [column_no], [true/false])

Parameters

  • value: It is the value to be looked up(searched)
  • range: It indicates the range within which the value must be searched.
  • column_no: The column number containing the return value.
  • true: If the user requires an approximate match.
  • false: If the user requires an exact match with the specified/given value

Performing a VLOOKUP (Similar to Excel) in R

Below are the ways to perform a VLOOKUP in R:

Method #1: Using Base R for Performing VLOOKUP

The following code explains how to use the merge() method to accomplish a function similar to VLOOKUP in base R.

Syntax of merge() function:

merge(dataFrame_1, dataFrame_2, by = "columnName")

Parameters

  • dataFrame_1, dataFrame_2: These are the dataframes
  • by: This is optional. It specifies multiple columns to merge

Example

Approach:

  • Create a data frame using data.frame()
  • Create a second data frame by passing some random goals.
  • Merge the above-created data frames with the common column in both data frame using the merge() function
  • The Exit of the Program.

Below is the implementation:

# create a dataframe using data.frame() 
dataFrameOne<- data.frame(section=LETTERS[1:18],
                        website=rep(c('BtechGeeks', 'SheetsTips', 'PythonPrograms'),
                                each=6))

# creating second dataframe by passing some random goals
dataFrameTwo<- data.frame(section=LETTERS[1:18],
                        goals=c(5,2,3,4,1,9,2,4,5,2,3,5,6,9,1,2,8,4))

# Merge the above created data frames with the common column in both dataframe using the merge() function
merge(dataFrameOne, dataFrameTwo, by="section")

Output:

   section        website goals
1        A     BtechGeeks     5
2        B     BtechGeeks     2
3        C     BtechGeeks     3
4        D     BtechGeeks     4
5        E     BtechGeeks     1
6        F     BtechGeeks     9
7        G     SheetsTips     2
8        H     SheetsTips     4
9        I     SheetsTips     5
10       J     SheetsTips     2
11       K     SheetsTips     3
12       L     SheetsTips     5
13       M PythonPrograms     6
14       N PythonPrograms     9
15       O PythonPrograms     1
16       P PythonPrograms     2
17       Q PythonPrograms     8
18       R PythonPrograms     4

Method #2: Using dplyr for Performing VLOOKUP

Here use the inner join function of the dplyr package in R to perform the same as the VLOOKUP function

Syntax of inner join function:

inner_join(dataFrame_1, dataFrame_2, by = "columnName")

Parameters

  • dataFrame_1, dataFrame_2: These are the dataframes
  • by: This is optional. It specifies multiple columns to merge

dplyr package:

The dplyr package in R contains several tools for manipulating data in R that provide a uniform set of verbs, helping to resolve the most frequent data manipulation challenges.

Installation:

install.packages("dplyr")

Output:

Installing package into ‘C:/Users/cirus/AppData/Local/R/win-library/4.2’
(as ‘lib’ is unspecified)
--- Please select a CRAN mirror for use in this session ---
trying URL 'https://cloud.r-project.org/bin/windows/contrib/4.2/dplyr_1.0.9.zip'
Content type 'application/zip' length 1290875 bytes (1.2 MB)
downloaded 1.2 MB

package ‘dplyr’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
C:\Users\cirus\AppData\Local\Temp\RtmpE3dtfs\downloaded_packages

Approach:

  • Import the dplyr package using the library() function
  • Create a data frame using data.frame()
  • Create a second data frame by passing some random goals.
  • Merge the above-created data with the common column in both data frames using the inner_join() function.
  • The Exit of the Program.

Below is the implementation:

# Import the dplyr package using the library() function
library(dplyr)
# create a dataframe using data.frame() 
dataFrameOne<- data.frame(section=LETTERS[1:18],
                        website=rep(c('BtechGeeks', 'SheetsTips', 'PythonPrograms'),
                                each=6))

# creating second dataframe by passing some random goals
dataFrameTwo<- data.frame(section=LETTERS[1:18],
                        goals=c(5,2,3,4,1,9,2,4,5,2,3,5,6,9,1,2,8,4))

# Merge the above created data with the common column in both dataframe using the inner_join() function
inner_join(dataFrameOne, dataFrameTwo, by="section")

Output:

  section        website goals
1        A     BtechGeeks     5
2        B     BtechGeeks     2
3        C     BtechGeeks     3
4        D     BtechGeeks     4
5        E     BtechGeeks     1
6        F     BtechGeeks     9
7        G     SheetsTips     2
8        H     SheetsTips     4
9        I     SheetsTips     5
10       J     SheetsTips     2
11       K     SheetsTips     3
12       L     SheetsTips     5
13       M PythonPrograms     6
14       N PythonPrograms     9
15       O PythonPrograms     1
16       P PythonPrograms     2
17       Q PythonPrograms     8
18       R PythonPrograms     4

 

Leave a Comment