1 / 15

How to use “MATCH” function in MS Excel | ExcelSirJi

ExcelSirJi is the top Microsoft Excel Tutorial offers free online excel courses, excel tricks, tips, online excel training, excel videos and more. Learn MS Excel today

excelsirji
Download Presentation

How to use “MATCH” function in MS Excel | ExcelSirJi

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. How to use “MATCH” function in MS Excel www.excelsirji.com

  2. MATCH function performs lookup for a value in a range and returns its position sequence number as output.MATCH Function has two required argument i.e. lookup_value, lookup_array and one optional argument i.e. [match_type]. Syntax: =MATCH(lookup_value,lookup_array,[match_type]) www.excelsirji.com

  3. Syntax Description: • lookup_valueargument is the used to give text/value/cell reference that you want to match • lookup_arrayargument is used to give the range in whichlookup_value to be matched • [match_type]is optional argument. 1,0,-1 values denote that how match function should behave. • 1 or Omitted= Less than [match_type] : Match finds the position of value which is “Equals to or Less than” the lookup value. lookup_array should be in Ascending order is prerequisites of this [match_type] • 0 = Exact match [match_type]: Match finds the position of value which is “Exact” the lookup value. Data sorting is NOT required for this [match_type] • -1 = Greater than [match_type]: Match finds the position of value which is “Equals to or Greater than” the lookup value. lookup_array should be in Descending order is prerequisites of this [match_type] www.excelsirji.com

  4. Example 1: MATCH function with 1 or Omitted [match_type] (i.e. Less than) Here, we have sample database and want to match the value withExact or Less than lookup_value: • “Column A” has series of values, • “Column B” shows the lookup_value argument value, that we want to match, • “Column C” shows the sample formula applied, • “Column D” shows the output of the function and, • Explanation is provided in “Columns E” www.excelsirji.com

  5. Image View www.excelsirji.com

  6. lookup_value is 9 and Less Than [match_type] (i.e. 1) is applied.Function will lookup the position of values for either 9 (i.e. Exact Match) or Less than 9. Data series did not contain 9 that is why position of 8 (i.e. Less than 9) is returned in output i.e. 5th position. www.excelsirji.com

  7. Example 2: MATCH function with 0 [match_type] (i.e. Exact match): • Here, we have sample database and want to match the values with Exact lookup_value: • “Column A” has series of values, • “Column B” shows the lookup_value argument value, that we want to match, • “Column C” shows the sample formula applied, • “Column D” shows the output of the function and, • Explanation is provided in “Columns E” www.excelsirji.com

  8. Image View www.excelsirji.com

  9. lookup_value is 9 and Exact [match_type] (i.e. 0) is applied. • Function will lookup the position of values for 9 (i.e. Exact Match). Data series contains 9 at 4th position and output is returned accordingly. www.excelsirji.com

  10. Example 3: MATCH function with -1 [match_type] (i.e. Greater than) • Here, we have sample database and want to match the values with Exact or Greater than lookup_value: • “Column A” has series of values, • “Column B” shows the lookup_value argument value, that we want to match, • “Column C” shows the sample formula applied, • “Column D” shows the output of the function and, • Explanation is provided in “Columns E” www.excelsirji.com

  11. Image View www.excelsirji.com

  12. lookup_value is 9 and Greater Than [match_type] (i.e. -1) is applied. • Function will lookup the position of values for either 9 (i.e. Exact Match) or Greater than 9. Data series did not contain 9 that is why position of 10 (i.e. Greater than 9) is returned in output i.e. 1st position. www.excelsirji.com

  13. Things to Remember: • – Function is NOT case sensitive, means it will match “A” for text contains “A” and “a” • – If no match is found in lookup_array then function output will return “#N/A” error • – Ascending order means, A-Z, a-z, -2,-1,0,1,2.. , False-True • – Descending order means, Z-A, z-a, 2,1,0,-1,-2.. , True-False www.excelsirji.com

  14. About us: “ExcelSirJiis the top most Microsoft Excel Tutorial offers free online excel courses, online excel training”.Please visit our website for more details and watch our videos on YouTube to find any Tips or Tricks of MS Excel Website: www.excelsirji.com YouTube Channel: Our other social platform is :

  15. ***Thank you***

More Related