Excel find duplicates formula how to#
This example looks at how to use the MATCH function if your list doesn't contain any duplicates. Using the MATCH function in Excel - find a match in a list without duplicates If your list doesn't contain your lookup_value, MATCH will return the position of the next smallest value. If your list contains your lookup_value more than once, MATCH will return the position of the last instance in the list of that value. A match_type value of -1 should be used when your list is sorted in descending order (largest to smallest).Important - MATCH will return a #N/A error if the lookup_value is not in the list.MATCH will return the position of the first occurrence of the lookup_value it finds, even if there are duplicates in the list MATCH will look for an exact match for your lookup_value in the list.A match_type value of 0 means your list doesn't need to be sorted.If you don't supply a match_type, Excel will automatically use 1 as the match_type.If your list doesn't contain your lookup_value, MATCH will return the position of the next largest value. If your list is not sorted you will sometimes get the right answer, but sometimes you'll get a #N/A error.If your list contains your lookup_value more than once, MATCH will return the position of the last instance of that value. A match_type value of 1 should be used when your list is sorted in ascending order (smallest to largest).Otherwise your MATCH functions won't always return the results you expect. Understanding the match_type parameter is the key to using the MATCH function effectively.Īlthough match_type is optional, I recommend you always set it to the correct value when using the MATCH function. Crucial - understanding the match_type parameter It's important to note that if your list doesn't include the lookup_value, the MATCH function will return a #NA error. The MATCH function finds the position of your lookup_value in the range of cells you're looking in (the lookup_array). If you leave it out Excel assumes a value of 1. It can have three possible values, -1, 0 and 1. match_type is an optional value that defines the type of match you are looking for.lookup_array is the range of cells that contain the list.lookup_value is the value you want to find in the list.=MATCH( lookup_value,lookup_array,match_type) The MATCH() function has the following syntax:
![excel find duplicates formula excel find duplicates formula](https://appauthority.com/wp-content/uploads/2021/07/excel-duplicates-2-1200x477-1.jpeg)
This lesson explains how to use the MATCH() function in Microsoft Excel, explains where you might use it, and provides a real world example of the MATCH() function in action.
![excel find duplicates formula excel find duplicates formula](https://cdn.extendoffice.com/images/stories/doc-excel/ttw-modules/select-duplicate-unique-values/ad-select-count-duplicates.png)
For example, in a list of weekdays starting with Monday first, MATCH() would return a value of 3 for Wednesday. The MATCH() function allows you to find the relative position of a value in a list in Excel.