MATCH Function

 MATCH Function

Summary

In MS Excel there are lot of lookup functions that can find a certain value in a range of cells, MATCH is one of them. Basically, it identifies a relative position or location of any item in a given range in the cells. But it has also a limitation that if there is a duplicate value, it gives the result which is found in the first number. MATCH can find or you can say that it supports only approximate, exact and Wildcard (* ?) matches.

MATCH is an Excel function used to locate the first position of a lookup value in a row, column or table. Often the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.

Purpose

Get the position of an item in an array

Return value

A number representing a position in lookup_array.

Syntax

=MATCH (lookup_value, lookup_array, [match_type])

Arguments

• lookup_value - The value to match in lookup_array.

• lookup_array - A range of cells or an array reference.

• match_type - [This is optional, means the formula works in a very accurate way if you does not use this option but it is recommended that it must be used for accurate result] 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest.

Usage notes

Use the MATCH function to get the position of a value in an array. Match offers three different matching modes which makes it more flexible than other lookup functions. When used with INDEX, MATCH can retrieve the value at the matched position. MATCH returns a position.

Match type information

Match type is optional. If not provided, match type defaults to 1 (exact or next smallest). When match type is 1 or -1, it is sometimes referred to as "approximate match". However, keep in mind that MATCH will find an exact match with all match types, as noted in the table below:


Note: Be sure to set match type to zero (0) if you require an exact match. The default setting of 1 can cause MATCH to return results that look "normal" but are in fact incorrect. I recommend always providing a value for match_type as a reminder of what behaviour is expected. Basic exact match when match type is set to zero MATCH performs an exact match. In the example below, the formula in E2 is:

Basic exact match

When match type is set to zero MATCH performs an exact match. Example:

=MATCH(B9,B2:B14,0)


 How the formula works

Put ‘=’ sign where you want to apply the formula then type MATCH formula after that open the bracket


Select the Toy name

Put the comma (,) to move to next criteria and select the whole row

The last option is a match type. Although this is an optional value, if you do not specify this, it will automatically put the value but in that case it may be possible that the specified condition does not fulfill your requirement so it is better to select that option too


So simply type here ‘0’ (Zero) to see the Exact Match and close the bracket


The result will be


Basic approximate match

When match type is set to 1 MATCH will perform an approximate match on values sorted A-Z, finding the largest value less than or equal to the lookup value. In the example shown below, the formula in F2 is:

=MATCH(B3,B2:B14,1)


Basic wildcard match

When match type is set to zero (0) MATCH can perform a match using wildcards. In the example shown below the formula in E3 is:

=MATCH(“b*”,B2:B13,0)


 If you find that at which no. that particular name exist, the MATCH function is very helpful


Notes

• MATCH is not case-sensitive.

• MATCH returns the #N/A error if no match is found.

• In case of duplicates MATCH returns the first match.

• If match_type is -1 or 1 the lookup_array must be sorted as noted above.

• If match_type is 0, the lookup_value can contain the wildcards.

• The MATCH function is frequently used together with the INDEX function.


Also see the Video regarding MATCH function


Recommended Articles



ND function

Comments

Post a Comment

Popular posts from this blog

TDS on Salary – Entry in Tally

Image
  Introduction TDS is Tax Deducted at Source. TDS is the amount of tax which is deducted by the employer from the taxpayer and is deposited to the Income Tax Department on behalf of him/her. The TDS rates are set on the basis of the age and income of different individuals. TDS or Tax Deducted at Source is a specific amount that is reduced when a certain payment like salary, commission, rent, interest, professional fees etc. is made. The person who makes the payment deducts tax at the source while the person who receives a payment/income has the liability to pay tax. It lowers tax evasion because the tax will be collected at the time of making a payment. Note: TDS is nothing but advance payment of income tax. When the TDS applied When you get your salary, TDS is deducted from your salary. Let’s for simplicity assume that the TDS on your salary is ₹20,000 and you are applying for the refund of the TDS because your income i.e. your salary is under the base limit for income tax wh
Read more

Post Dated Cheque (PDC) Management

Image
  Introduction Post Dated Cheque is an essential part of today’s business world. For a better financial planning and fund management PDC cheque is a must tool to survive in a tight financial conditions of a competitive world. A cheque in which a future date is written on its face is called Post Dated Cheque or popularly known as PDC. PDC can be issued to your suppliers and can be received from your customers. Normally Post-dated cheques are presented on the date of Cheque and booked or accounted on the same date. If you have a large number of PDC to manage then it is annoying for accountant to keep track of cheques. Tally ERP 9 take charge of the PDC Management for you and let you do a tension free accounting. You can enter post-dated cheques into receipt voucher, payment voucher and contra vouchers. Benefits of Post-dated Cheque Transactions 1.    You can enter the cheque into tally immediately after receiving or issuing it. 2.    You can mark a cheque as post-dated which wi
Read more

Personal Accounts in Tally

Image
 Introduction You can easily maintain personal accounts in Tally. To maintain personal accounts in Tally is very easy and simple. It is much faster than maintaining accounts for a company. The reason is that personal accounts are comparatively very small and involve only a few amounts of entries. Now compare that to a fully-fledged company which is selling products and includes all kinds of accounting entries and on the top of that, there is GST accounting entries too. But when you maintain personal accounts in Tally, there is generally no GST involved and you need to just focus on your balance sheet. So, let’s get started step by step. Maintain Personal Accounts in Tally When you have to maintain personal accounts in Tally, you should mainly focus on balance sheet. There is a specific reason for that. In personal account, there is no business account involved because it is your personal accounts. Therefore, there is NO need for a Profit and Loss Account. If you have a business, It
Read more