WPS Office

Free All-in-One Office Suite with PDF Editor


Edit Word, Excel, and PPT for FREE.


Read, edit, and convert PDFs with the powerful PDF toolkit.


Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android


How to use match function in excel to compare two columns

July 26, 2022

The Excel Lookup and Reference functions section includes the MATCH Function. It retrieves the location of a value within an array after looking up the value there. For instance, the function will return 2 because 5 is the second item in the range A1:A4, which comprises the integers 1,5,3,8, and 10. The MATCH function, in conjunction with other functions, can be used in financial analysis to look up and return the total of values in a column. It frequently goes along with the INDEX function. Learn to mix INDEX MATCH to perform powerful lookups.

MATCH is one of the numerous unique lookups/reference functions available in WPS Excel that may be used to locate a specific value inside a set of cells. In essence, it pinpoints the location or relative position of any object within a set of cells. The MATCH, however, is far more versatile than its basic functionality.

In a table or row column, the MATCH function is used to locate a lookup value. MATCH finds restricted matches with wildcards (*?) and approximate and exact matches. To access the value at the place returned by MATCH, the INDEX function is typically combined with a MATCH function.


=MATCH(lookup_value, lookup_array, [match_type])

These are the arguments that the MATCH formula employs:

Lookup value is the value that needs to be looked up (mandatory parameter).The data array that is to be searched is denoted by the argument lookup array (needed).(Optional Argument) Match type To return results, it can be set to one of the following values: 1, 0, or -1.How to use Match function in excel online, 2016 and 2019:Let's look at a few instances to better understand the function's applications:1.Let's say we have the following information:

2.To determine the value for cucumber, we can apply the formula below.

3.The outcome is as follows:

How to use Match function in excel using another example:Let's say we have the following information:

1.Let's say we want to know how many pairs of pants there are that particular hue. We'll employ the INDEX or MATCH functions in this case. The appropriate array formula is:

2.Use the keyboard shortcuts CTRL + SHIFT + ENTER to construct an array. The outcome is as follows:

Things to remember while using Match function in excel:When matching text values, the MATCH function does not make a distinction between uppercase and lowercase letters.If the match function is unable to locate a match for the lookup value, a N/A! error will be generated.The function allows partial matches with wildcards (* or?) and approximate and exact matching.Note: This above written article is an attempt to show you how to use match function in excel online, 2016 and 2019, in both windows and mac.You just need to have a little understanding of how and which way things work and you are good to go. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.                                  

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.