Skip to content

5000+ Great Articles

Best Articles & Essays: Interesting Articles to Read Online

  • Free Download Noorani Qaida islamic Books
  • What is VPN, and Why You Should Use it? How-To
  • Test Your Browser’s Security 2022 Computer Tips
  • How to Change a Drive Letter in Windows How-To
  • How To Use The Tabbed Command Prompt In Windows 10 How-To
  • How to Fit a Whole Video on YouTube Story How-To
  • Why Is Graphing Calculators So Famous? Uncategorized
  • Delete or Clear Jump List Recent Items in Windows 7, 8 & 10 Computer Tips

How to Find Matching Values in Excel

Posted on October 5, 2020 By blog_786 No Comments on How to Find Matching Values in Excel

You have an Excel workbook with thousands of numbers and words. There must be numbers that are multiples of the same number or word. You may need to find them. So, we’ll look at several ways to find matching values ??in Excel 365

We’re going to find the same words or numbers on two different sheets and two different columns. We will look at using the EXACT, MATCH and VLOOKUP functions. Some of the methods we’ll be using may not work in the web version of Microsoft Excel, but they will all work in the desktop version.

How to Find Matching Values in Excel

What is the Excel function?

If you’ve used features before, please skip.

The Excel function is like a widget. It uses a series of steps to complete a single task. The most commonly used Excel functions can be found on the Formulas tab. Here we can see that they are grouped by the nature of the function –

  • AutoSum
  • Recently Used
  • Financial
  • Boolean
  • Text
  • Date and time
  • Search and reference information
  • Math and triggers
  • Additional functions.

How to Find Matching Values in Excel

The Advanced Features category contains the Statistics, Development, Cube, Information, Compatibility, and Internet categories.

Exact function

The job of the Exact function is to go through the rows of two columns and find the matching values ??in the Excel cells. Exact means exact. The Exact function itself is case sensitive. He does not believe that New York and New York correspond to each other.

In the example below, there are two columns of text – tickets and receipts. For a total of 10 sets of text, we could compare them by looking at them. However, imagine there were 1000 lines or more. That’s when the Exact function can be used.

How to Find Matching Values in Excel

Place the cursor in cell C2. In the formula bar, enter the formula

= EXACT (E2: E10, F2: F10)

How to Find Matching Values in Excel

E2: E10 refers to the first column of values, while F2: F10 refers to the column next to it. After we hit the Enter key, Excel will compare the two values ??in each row and tell us if they are the same (True) or not (False). Since we used ranges instead of two cells, the formula will jump to the cells below it and evaluate all other rows.

How to Find Matching Values in Excel

However, this method is limited. It will only compare two cells that are in the same row. For example, it won’t compare what’s in A2 to B3. How do we do it? MATCH can help.

The MATCH function

MATCH can be used to tell us where the match for a specific value is in a range of cells.

Suppose we want to know which row a particular SKU (stock holding unit) is on in the example below.

How to Find Matching Values in Excel

If we want to know which line AA003 is on, we have to use the formula:

= MATCH (J1, E2: E9,0)

How to Find Matching Values in Excel

J1 refers to the cell with the value we want to match. E2: E9 refers to the range of values ??we are looking for. The zero (0) at the end of the formula tells Excel to find an exact match. If we were to match numbers, we could use 1 to find something smaller than our query, or 2 to find something larger than our query.

How to Find Matching Values in Excel

But what if we want to know the price of AA003?

VLOOKUP

The V in VLOOKUP stands for vertical position. This means that it can search for a given value in a column. What it can also do is return a value on the same line as the found value.

If you have an Office 365 subscription in a monthly channel, you can use the new XLOOKUP version. If you only have a semi-annual subscription, it will be available in July 2020.

Let’s use the same inventory data and try to find out the price of something.

How to Find Matching Values in Excel

Where we were looking for a string earlier, enter the formula:

= VLOOKUP (J1, E2: G9,3, FALSE)

How to Find Matching Values in Excel

J1 refers to the cell with the value we are matching. E2: G9 is the range of values ??we are working with. But the VLOOKUP function will only look for a match in the first column of that range. Number 3 refers to the third column from the start of the range.

Therefore, when we enter the SKU in J1, the VLOOKUP function finds a match and takes the value from a cell 3 columns from it. FALSE tells Excel which match we are looking for. FALSE means it must be an exact match, while TRUE means it must be close.

How to Find Matching Values in Excel

How do I find matching values ??on two different datasheets?

Each of the above functions can work on two different worksheets to find matching values ??in Excel. We’re going to use the EXACT feature to show you how to do this. This can be done with almost any function. Not just the ones we’ve covered here. There are other ways to link cells between different sheets and workbooks

Working on the “Holders” sheet, enter the formula

= EXACT (D2: D10, Tickets! E2: E10)

How to Find Matching Values in Excel

D2: D10 is the range we selected on the “Holders” sheet. After we put a comma after that, we can click on the ticket sheet, drag and drop and select the second range.

How to Find Matching Values in Excel

See how he refers to the sheet and range as tickets! E2: E10? In this case, all rows match, so all results are correct.

How to Find Matching Values in Excel

How can I also use these functions?

Once you have mastered these functions for matching and finding things, you can start doing a lot of different things with them. Also, note the combination of the INDEX and SEARCH functions to do something similar to VLOOKUP.

Are there some cool tips for using Excel functions to find matching values ??in Excel? Maybe the question is how else to do it? Drop us a line in the comments below.

–

Share this:

  • Twitter
  • Facebook
MS Office Tips

Post navigation

Previous Post: What Is The Best Video Conferencing App? 4 Compared
Next Post: How To Clean Your Phone To Prevent The Spread Of Bacteria

Related Posts

  • Link Cells Between Sheets and Workbooks In Excel MS Office Tips
  • Charting Your Excel Data MS Office Tips
  • How to Share or Collaborate On a Word Doc with Others MS Office Tips
  • The Best Microsoft PowerPoint Keyboard Shortcuts MS Office Tips
  • How to Add Comments to an Excel Worksheet Cell MS Office Tips
  • Add a Cover Page to a Word Document MS Office Tips

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Archives

  • September 2023
  • August 2023
  • July 2023
  • June 2023
  • May 2023
  • April 2023
  • November 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • March 2021
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • December 2019
  • July 2019
  • May 2019
  • April 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018

Categories

  • AI Tools & Guides
  • Amazon Web Services
  • Apple Watch
  • Computer Tips
  • Cool Websites
  • Featured Posts
  • Free Software Downloads
  • Gadgets
  • Gaming
  • General Software
  • Google Software/Tips
  • Hardware
  • Help Desk
  • How-To
  • iOS
  • iPad
  • iPhone
  • islamic Books
  • Linux
  • Linux Tips
  • Mac OS X
  • macOS
  • MS Office Tips
  • Networking
  • Office Tips
  • OS X
  • Product Reviews
  • Reviews
  • Safari
  • Smart Home
  • Smartphones
  • Software Reviews
  • technology
  • text
  • Tools Review
  • Troubleshooting
  • Tutorials
  • Uncategorized
  • Urdu Books PDF
  • Web Site Tips
  • Windows
  • Windows 10
  • Windows 7
  • Windows XP Tips
  • Wordpress
  • 5 Cool Websites to Find Good Movies and TV Shows on Netflix
  • SmartDNS vs VPN – What’s the Difference?
  • How to go Back to the old YouTube Layout (2017)
  • A Simple Trick to Get 50% Discount on Audible for Three Months
  • What Is “Other” Storage on Android And How to Clean It Up ?
DMCA.com Protection Status

Recent Posts

  • How to Bypass Chromecast DNS and Circumvent Geo Blocking
  • 5 Cool Websites to Find Good Movies and TV Shows on Netflix
  • SmartDNS vs VPN “What’s the Difference?”
  • How to go Back to the old YouTube Layout (2013)
  • A Simple Trick to Get 50% Discount on Audible for Three Months

Recent Comments

  1. A Simple Trick to Get 50% Discount on Audible for Three Months on Private Browsing: What is it and What it is not
  2. 6 Best PayPal Alternatives (2017) on How to Save Money While Shopping Online in India
  3. Automatically Transcribe YouTube Video/Audio with Google Docs on 5 Best Team Management Apps (For Small and Large Teams)
  4. 6 Things You Need Know About Email Encryption on Delete All Emails from Gmail With Once Click
  5. Looking For YouTube Alternative? Try These 7 Video Sharing Sites on Ten Best YouTube Video Editing Software 2023
  • Best gp189 calculator battery 2020

    Best gp189 calculator battery 2020. 1 - Rayverstar LR1130 AG10 1.5V Alkaline, (20-Batteries) Fits: L1131, 189, 389, 390, 534, 554, 603 (Full List Be

  • How to Fix No Sound on iPhone iPhone
  • How To Remove Viruses Before Your Operating System Starts How-To
  • speed up network file transfer windows 10 Help Desk
  • 7 Ways To Access Your Computer’s Files Remotely Over the Internet How-To
  • Philips Hue vs the Competition – Which are the Best Smart Lights? Smart Home
  • How To Watch Blue Is The Warmest Color on Netflix How-To
  • Save a List of Running Processes to a Text File in Windows How-To

Copyright © 2023 How To Blog.

Powered by PressBook News WordPress theme

Go to mobile version