Open In App

How to Find Duplicate Values in Excel Using VLOOKUP?

Last Updated: 06 Jun, 2021

V

Vibhanshu Pandey
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

In this article, we will look into how we can use the VLOOKUP to find duplicate values in Excel.

 To do so follow the below steps:

  • Let’s make two columns of different section to check VLOOKUP formula on columns:

Created Two Columns 

  • Here is the formula we are going to use:
=VLOOKUP(List1,List2,TRUE,FALSE)
  • In this formula, the List-1 names will be searched in List-2. If there exists any duplicate name, the formula will return the name from List-1. Let`s look closely at our example for better clarification.
  • In C2 we will write this formula =VLOOKUP(A2:A10,B2:B10,TRUE,FALSE) and then press enter.

  • Now, we will see this result:

Here Himesh is duplicate value

  • Here the Himesh is found because the VLOOKUP function searches this name from Section A to Section B. When the same name is found it will output the result from Section A.
  • Now drag the list and see all values.

Here we drag the formulated Cells

  • The #N/A results are found because, in those particular cells, the names from column A are not found in column B.
  • In the Result column, you’re seeing a total of 2 duplicate values ( Himesh, Pragya). #N/A values are representing the unique values of column Section A.

Using VLOOKUP to find duplicate values in two Excel worksheets

We can also use the VLOOKUP to find duplicate values between two Excel worksheets. To do so follow the below steps:

  • Make two excel workbooks Section A and Section B.

Two Workbooks

  • In the B2 of Section B type the below code and press enter:-
=IF(ISERROR(VLOOKUP(A2,'Section A'!A1:A10,1,0)),"Unique","Duplicate")

Section A and Section B on different workbooks

Using VLOOKUP to find duplicates in two Workbooks of Excel

We can also use the VLOOKUP to find duplicate values between two Excel workbooks. To do so follow the below steps:

  • Now we create a new Section A in a new Workbook with different values.

New Workbook

  • And In our main workbook which we were working (in our last example), create another worksheet titled Sheet 1 and again create a list of products.

Another workbook named Sheet1

  • Now in cell C2 of Sheet1, write down the following formula and press enter.
=IF(ISERROR(VLOOKUP(B2,[VL]Section A!$A$2:$A$10,1,0)),"Unique", "Duplicate")
  • You will get the result as Unique because value does not exist in Section A.

Result

  • Now Drag Down the formulated cell to see out the result of the whole column.


Article Tags :

V

Vibhanshu Pandey
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

K

Kritidipta Ghosh

Excel VLOOKUP Function - Excel Guide for Beginners

article_img
How to do Vlookup in Excel - Quick StepsPrepare Your DataEnter the VLOOKUP Formula >>Press EnterUse a Cell Reference for Flexibility Copy the Formula for Multiple RowsThe VLOOKUP function is one of the most widely used tools in Excel for looking up and retrieving data from a table. Whether you’re working with a single sheet or multiple workbooks, VLOOKUP allows you to search for a value in one column and return a related value from another. If you're new to Excel or still getting the hang of formulas, this guide is for you. We will cover everything from the basics of how VLOOKUP works to practical examples that show how to search data across sheets, troubleshoot common issues, and master the VLOOKUP formula for any task.Excel VLOOKUP FunctionWhat is the VLOOKUP Function in ExcelThe term VLOOKUP stands for Vertical Lookup. It is designed to search for a specific value in the first column of a table (lookup column) and retrieve corresponding data from a different column in the same row.Syntax of VLOOKUP FormulaVLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Arguments:1. lookup_value: The value you want to search for (e.g., a specific ID, name, or product code).
Read More

E

Eleeka

How to VLOOKUP to Return Multiple Values in One Cell in Excel?

article_img
Almost everyone works with lots of data ranging from medium to high volume datasets, and extracting information from such a sheet can become easier, with little to advanced knowledge of excel. Excel is widely used to perform simple to complex data analysis. It is a great tool for generating meaningful & insightful reports for quick decision-making. So, out of hundreds of functions available in Excel, the chosen one for today's article is the most common & popular "VLOOKUP". VLookup Fetching the relevant value from a table or a range by row is made possible with VLOOKUP. For the benefit of all, let's quickly describe VLOOKUP with the syntax given below. It returns one value from the first match in the same row. = VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) VLOOKUP SyntaxVLOOKUP to Return Multiple Values in One Cell in Excel Below given screenshot shows that VLOOKUP returns only one value but, there are multiple entries for the same person. The goal here is to return all the items sold by one person in one cell. There are various options however, let's look at 2 methods to achieve the goal: Method 1: Using "VLOOKUP" with some workaround and a few more
Read More

A

Ankit kumar

How to Return Multiple Matching Rows and Columns Using VLOOKUP in Excel?

article_img
VLOOKUP function is a premade (already made by Ms-Excel) function in Excel by which we can search for any information in a given spreadsheet. We can use the VLOOKUP function in two ways, first is VLOOKUP with an exact match and VLOOKUP with an approximate match. VLOOKUP with exact match means that kind of data from which we can able to find same to same i.e; exact value from the given table. But VLOOKUP with the approximate match is something in which we can not able to find exact value from data But we can find approximate value, for eg., when we are finding the grade of students for their results then that grade comes from approximation means that grade lies between some value i.e; marks 50 - 60 lies under Grade B and if some student got 55 marks then they lie under grade B for that particular subject, So, here approximation occurs, which lies in VLOOKUP with an approximate match. Whenever we use any formula in excel the syntax is =FormulaName and by default, formulas are always in uppercase only, although we can write in lowercase also, for eg. =VLOOKUP(). Returning Multiple Matching Rows and Columns Using VLOOKUP VLOOKUP function has four parameters VLOOKUP(lookup_value, table
Read More

G

Gaurav Sharma

Using CHOOSE Function along with VLOOKUP in Excel

article_img
In Excel, managing space is a very important point to consider. Now, let's suppose you are in a situation where you want to compare multiple columns in the Excel dataset, and you want to derive results, This is not possible using the VLOOKUP function alone. So, to make a function capable of doing something like this, we will introduce a special function known as CHOOSE function. But, if you don't want to use CHOOSE function, then you have to use a helper column, making the job a little hectic. So, in this article, we will see how to use VLOOKUP along with CHOOSE function. First, we will see the syntax of CHOOSE FUNCTION in brief. CHOOSE FUNCTION: Syntax: =CHOOSE(ARRAY DIMENSIONS(INDEX VALUE(S)),VALUE(1),VALUE(2),VALUE(3),.....) Here, Here we will define array dimensions, ie how many columns should the array have.The next parameter(s) will define what value will go in the subsequent array columns, ie VALUE1 will get filled in the first column, and so on. Example: Here we will use this dataset. Let's suppose we have people in a sales firm, and they are evaluated for bonuses. Now, we want to know who did sales for BED SHEETS, by using VLOOKUP and CHOOSE function. So, first of all,
Read More

M

Midhu M

Excel VLOOKUP with Dynamic Column Reference

article_img
Suppose anytime you work with colossal tables of data and you want to implant a VLOOKUP function that dynamically updates to the accompanying portion as you copy it across. In that case, the VLOOKUP with the COLUMNS capacity is what you truly care about. That is; the col_index_num a piece of the VLOOKUP capacity intensely revives as you copy it across your worksheet. Before learning VLOOKUP with Dynamic Column Reference, let's first learn what is VLOOKUP function, VLOOKUP VLOOKUP is utilized when one necessities to track down the worth of a cell/data of that cell that is available elsewhere (can likewise be available in another sheet). The following is the capability of VLOOKUP, =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) Example: Okay, we ought to imagine this summary is hundreds or even a considerable number of sections long and we really want to find a quick strategy for getting expenses and maker information on demand (other than CTRL+F for Find clearly). In the table underneath we have the Book Title, Author, and Price. The book title is found utilizing a Data Validation List or Drop Down List, and in sections C and D we want a VLOOKUP equation that finds t
Read More

D

Dwaipayan Bandyopadhyay

How to VLOOKUP to Return Blank or Specific Value Instead of 0 or N/A in Excel?

Excel is widely used for data management, and practically everyone utilizes it these days. At the same time, it is extremely user-friendly. Excel offers a number of functions and formulae that may make boring tasks much easier. VLOOKUP() is another extremely helpful method that is commonly used to 'look up' any data from a large table or a range by row. It allows for approximate or precise matching dependent on whether one of its arguments is 'True' or 'False'. In general, the VLOOKUP() method is used to retrieve data from a table, list, or range. It primarily offers two features: approximate match and exact match.  In this article, we will see how to Return a Blank or a Specific value instead of 0 or N/A in Excel. There are several ways to achieve that. All are discussed below. Method 1: Using IF and VLOOKUP togetherReturning Blank in Place of 0: Using the IF and VLOOKUP together we can return a Specific Value or Return Blank.Dataset used in this Tutorial Syntax of IF function in Excel -  = IF (CONDITION, [VALUE_IF_CONDITION_TRUE], [VALUE_IF_CONDITION_FALSE]) Explanation:  CONDITION - Conditions that yields either True (1) or False (0) values. For example - say we want to check if
Read More

I

Ishika

How to Apply Conditional Formatting Based On VLookup in Excel?

VLOOKUP is an Excel function to lookup data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Conditional Formatting Based on Vlookup: 1. Using the Vlookup formula to compare values in 2 different tables and highlighting those values which is only present in table 1 using conditional formatting. We have a Table containing old products of any grocery shop in 'Old Product' sheet and an updated table having new products in worksheet 'New'. We want to highlight rows in New table containing those items which are not in Old Product Table. Old Product Select the data from New table except the Headers. (The table in which we want to highlight rows.)Go to Home->Conditional Formatting->New Rule. In the dialog box appeared, Select the rule type - "Use a formula to determine which cells to format;";Under Edit the rule description enters the following formula: =ISNA(VLOOKUP($A2,'Old Product'!$A$1:$B$8,1,FALSE))Then click Format. Formula explanation: Inside VLOOKUP, 1st parameter is $A2 which is first name in New table.2nd parameter is Old Product Table.3rd parameter is column we want to compare which is 1 as we wan
Read More

G

Gaurav Sharma

Different Ways to Perform a Case-Sensitive Vlookup in Excel

article_img
In Excel datasets, sometimes you must have noticed, that there are many values in a table that are case-sensitive in nature. And if we want a particular type of value from our dataset, but unfortunately that value also has some duplicates, but with different case sensitivity, so here we need to search on the basis of case sensitivity. To make conditions worse, you only know one method to perform the search functions,i.e none other than VLOOKUP, and in nature, it is case-insensitive nature. What will you do? The only option to perform VLOOKUP in case of insensitivity is to optimize it using different methods. But first, let us see how VLOOKUP is case-insensitive, by using an example: Now, let's suppose we have this dataset of a company, and what we want, is how much "KaTy" has earned, and we only know the VLOOKUP function to perform it. So, applying VLOOKUP straightforward to find "KaTy" is not possible. Nonetheless, the output of applying VLOOKUP is going to be VLOOKUP will look for approximation, rather than going for an exact match.VLOOKUP will look for the first encountered value and will return it. This is a problem, as first, we want an exact match to the keyword provided, and
Read More

G

Gaurav Sharma

VLOOKUP On Multiple Criteria Columns Using Helper Method & CHOOSE Function

article_img
In this article, we will see how we can combine multiple values, and use them as lookup criteria for searching specific value(s) in the excel dataset. For this purpose, we will use the VLOOKUP formula, the most basic formula to perform a lookup for value(s) on excel. We will see a modified form of VLOOKUP to perform a lookup using multiple values. First, let us see a general formula: Formula: =VLOOKUP(v1&v2,dataset_part,column,0) Here, Here v1 and v2 signify those two cells, which will be used for lookup in combination.The Dataset_part signify the starting cell value and the ending cell value, upon which lookup is to be performed. This can consist of the whole dataset as well.The column signifies from which column the value has to be picked up.Here 0 signifies the approximation of lookup in vlookup. It can be set to 1 to enable approximation. Now, let us see in brief, or a summary, how VLOOKUP will work in the Multiple Criteria Column. Here, we have 2 ways of modifying VLOOKUP to make it capable of doing a lookup on multiple criteria, they are: Helper Column.CHOOSE function. Helper column will be a sort of redundant work, as we have to provide merged values of those columns on
Read More

M

Midhu M

Index Match Vs VLOOKUP: Which one is Better, Formula Differences and more

article_img
Index Match vs VLOOKUP - Key DifferencesINDEX/MATCH offers more stability because it locks in the exact column you want to retrieve, unlike VLOOKUP, where changing columns in the lookup range can alter the returned results. INDEX/MATCH is structurally stronger since moving columns around won’t disrupt your results, whereas VLOOKUP might display incorrect data if columns shift. If your data is stable, doesn't involve sensitive information, and you’re referencing only a few columns, VLOOKUP works well. However, for most other cases, INDEX/MATCH is the safer, more reliable choice.When comparing INDEX MATCH vs. VLOOKUP in Excel, one key difference lies in how they handle column references. VLOOKUP relies on a fixed column number, which can lead to errors if columns are rearranged, making it less flexible for dynamic datasets. On the other hand, INDEX MATCH uses a dynamic column reference, automatically adjusting to changes in your data, which is especially beneficial for larger and more complex spreadsheets. Both functions are powerful tools for performing lookups in Excel, each with unique strengths and limitations. Whether you're analyzing complex datasets, creating dynamic reports,
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox