With this post, my aim is to help you Locate information using Match Function in Excel. In the practice file of this video lesson, I have three sheets:
- See it: This is where I will demonstrate the lesson.
- Do it: This sheet is for you to practice.
- Dataset: This sheet is where we have the data we are using to demonstrate the lesson.
The Match function searches for an item in a row or a column and then returns its position from a given range. The Function is designed to search for a specified item in a range of cells either in a single row or a single column. It will not function properly when tried on a matrix.
Match Function Arguments
The match function takes in 3 arguments.
- lookup_value: What are you looking for?
- lookup_array: Which row or a column do you want to look inside?
- match_type: This is an optional value that specifies how accurate the returned value should be. Refer the table below for more details on match_type argument:
|1 or omitted||MATCH finds the largest value that is less than or equal to lookup_value. The values must be placed in ascending order, for example: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.|
|0||MATCH finds the first value that is exactly equal to lookup_value; the argument can be in any order.|
|-1||MATCH finds the smallest value that is greater than or equal tolookup_value. The values must be placed in descending order, for example TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.|
Please go ahead and watch the video below to learn how to Locate information using Match Function in Excel.
Excel Learning Series – TOC
- Excel 2016 – Basics – Find Sum, Average, Min and Max
- Excel 2016 Difference Between Formulas and Functions
- Excel 2016 Copy Formulas or Functions
- Microsoft Excel Auto Fill
- Excel 2016 Manage Views using Freeze Panes
- Microsoft Excel – Saving Workbooks
- Excel Upper and Lower Text Functions
- Excel 2016 – Text Function – Proper
- Excel Text Functions – Trim Function
- Excel – Text Function Left Function
- Excel Text Function – Right Function
- Excel Text Functions – MID Function
- Excel Concatenate Text Function
- Excel Nested Functions
- Excel Search Function
- Excel – Calculate Age using Date Function Today
- Calculate Tenure in Excel with Days Function
- Autofill Dates in Excel
- Formatting Dates in Excel
- Excel Date Format Shortcut
- Calculating Sum of Time in Excel
- Calculate Expiry Date in Excel 2016 – EDATE
- Find Age or Tenure using DATEDIF in Excel 2016
- RandBetween – Generate Random Dates in Excel 2016
- Excel Weekday Function
- Excel WeekNum and ISOWeekNum Functions
- Excel Networkdays and Excel Networkdays.INTL
- Excel Workday and Workdays INTL Functions
- Excel DATEVALUE AND TIMEVALUE
- Calculate Time Difference in Excel
- Calculate Quarter in Excel
- Rounding Day, Hour and Minutes in Excel
- Excel Pay Rate and Downtime Cost Calculation
- Excel Logical Functions IF, AND, OR
- Calculate Bonus in Excel Using IF Function
- Excel Learning – SumIF and SumIFS
- How to use Excel Count Functions
- How to use VLOOKUP in Excel
- How to vLookup in a different workbook in Excel
- VLookup for Multiple Sheets with Example
- Excel Custom Quarters
- Vlookup Return Custom Value
- Vlookup Based on Dropdown Selection
- HLookup in Excel
- Locate information using Match Function in Excel
We hope you find this post useful. In case you are stuck and or need help, feel free to drop a comment below or submit your question. Do share it further and add your feedback about the article in the comments section.