Excel Match Function - Another Lookup Function

Excel Match function is another lookup function that can use to find out the position of an item in a range. Below data represent the type of mobile phone using by the managers of ABC company.

Pone Type Count
Samsung 35
Sony 30
Sony Ericsson 12
Nokia 28
HTC 15
Blackberry 5

1. Copy above data to your excel sheet or download the file here.
2. Enter "=match(" to the cell D1
3. Then Enter lookup value (30 for this example)
4. Then enter lookup array which you want to search your lookup value
5. Here our count data are in the column B & select B2:B7 range as lookup range
6. Then excel ask for the match type. There are 3 types of matches in excel
   1. Exact match
      1. Excel finds the exact lookup value from lookup array
      2. The array can be any order
   2. Less than match
      1. Excel finds the largest value less than to the lookup value.
      2. The array must be in ascending order
   3. Greater than match
      1. Excel finds the smallest value that is greater than to the lookup value
      2. The array must be in descending order
7. Finally Enter the match type you want and close the function.
8. If you use the exact match you will receive the value 2

Advance Uses of Microsoft Excel If Function

Excel if function in Microsoft excel is a basic function which is used by most of us for various formulas. If you want to learn about this function you can buy books about the Microsoft excel by eBay or Amazon stores. However this is a cost for you and is not given an instant solution to your issue. Read below to learn the basics about sumif, averageif functions or find maximum when we have to use if to filter data. Follow below steps to learn how doing it. Let's assume that company ABC is doing computer related sales and data sheet extracted from it's cash book.

averageif

1. Download the excel file from here (or you can type the data into excel file in your computer.) 
2. Then Select the cell J1
3. Now Enter the "=averageif(" (If you need filter data using 2 or more conditions use "averageifs(" )
4. Then Select the range which you wish to have your condition. For this example it is a cell range which has names (our condition is "Asela"). Select the range E2:E13.
5. Then we have to enter the condition. Our condition is "Asela" and enter it within inverted commas.
6. Finally enter the cell range which has the values to add. For this example it is D2:D13.
7. After that Press enter and check whether your answer is equal to $196.43. If it is your formula is correct. If not check your formula.

sumif

1. Select the cell J5
2. Then Enter the function "=sumif (" (If you need filter data using 2 or more conditions use "sumifs(" )
3. After that Follow the steps 4, 5, 6 and 7 in above example. (Note that here out condition is "Monday")
4. Your answer should be $825

Find Maximum or Minimum With Condition

When you try to use maxif or minif as we done earlier for average or sum you can notice that there is no maxif or minif function. Then how we find the maximum or minimum with the condition? The answer is use array formula which includes max (min) and if functions. Follow below steps to do this.

1. Select Cell J9
2. Then Enter the "=max(" (or "=min(" )
3. Now enter "if(" (now formula bar shows "=max(if(" )
4. After that enter the condition as we enter to the "if" function. We are going to find maximum sales value in Friday. Therefor our condition is "B2:B13="Friday" ".
5. Then select the range which is to be look if the condition is true. Since are going to find maximum sale value our range is D2:D13
6. Now close the two functions "if" and "max" and press enter.
7. This time you got wrong answer because we didn't activate the array function yet. To do that follow below steps.
8. Then select the cell J9
9. Now press F2 Key
10. Press Ctrl + Shift + Enter
11. Now you have the right answer which is equal to $60