Chapter 14

WORKING WITH FORMULAS AND FUNCTIONS CHAPTER - 14: USING FORMULAS FOR MATCHING AND LOOKUPS Structure 1.0 Learning Objectives 1.1 Introduction 1.2 Match Function 1.3 Index Function 1.4 LOOKUP function 1.5 Summary 1.7 References 1.0 Learning Objectives After studying this unit, you will be able to:  Explain the match function in MS Excel?  Explain the Index function in MS Excel  Explain the LookUp function MS Excel 1.1 Introduction INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH is incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria. If you want to improve your Excel skills, INDEX and MATCH should be on your list. Page 31 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.2 Match Function The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, the MATCH function below looks up the value 53 in the range B3:B9. Syntax: MATCH(lookup_value, lookup_array, [match_type]) The MATCH function syntax has the following arguments:  lookup_value: Required the value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want. The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.  lookup_array: Required the range of cells being searched.  match_type: Optional the number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1. Page 32 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.3 Index Functions The INDEX function in Excel is fantastically flexible and powerful, and you'll find it in a huge number of Excel formulas, especially advanced formulas. Actually, in a nutshell index retrieves the value at a given location in a range. For example, let's say you have a table of planets in our solar system (see below), and you want to get the name of the 4th planet, Mars, with a formula. You can use INDEX like this: =INDEX(B3:B11,4) INDEX returns the value in the 4th row of the range. INDEX gets a value at a given location in a range of cells based on numeric position. When the range is one-dimensional, you only need to supply a row number. When the range is two-dimensional, you'll need to supply both the row and column number. 1.4 LOOKUP functions Use LOOKUP, one of the lookup and reference functions, when you need to look in a single row or column and find a value from the same position in a second row or column. Page 33 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS For example, let's say you know the part number for an auto part, but you don't know the price. You can use the LOOKUP function to return the price in cell H2 when you enter the auto part number in cell H1. Use the LOOKUP function to search one row or one column. In the above example, we're searching prices in column D. There are two ways to use LOOKUP: Vector form and Array form  Vector form: Use this form of LOOKUP to search one row or one column for a value. Use the vector form when you want to specify the range that contains the values that you want to match. For example, if you want to search for a value in column A, down to row 6. Page 34 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS  Array form: The array form is provided for compatibility with other spreadsheet programs, but it's functionality is limited.An array is a collection of values in rows and columns (like a table) that you want to search. For example, if you want to search columns A and B, down to row 6. LOOKUP will return the nearest match. To use the array form, your data must be sorted. Syntax: LOOKUP(lookup_value, lookup_vector, [result_vector]) The LOOKUP function vector form syntax has the following arguments:  lookup_value Required. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.  lookup_vector Required. A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.  result_vector Optional. A range that contains only one row or column. The result_vector argument must be the same size as lookup_vector. It has to be the same size. Page 35 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.5 Summary  The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.  In a nutshell index retrieves the value at a given location in a range.  Use LOOKUP functions, when you need to look in a single row or column and find a value from the same position in a second row or column. 1.6 References https://exceljet.net/index-and-match https://www.excel-easy.com/examples/index-match.html https://www.ablebits.com/office-addins-blog/excel-match-function/ https://support.microsoft.com/en-us/office/match-function-e8dffd45- c762-47d6-bf89-533f4a37673a https://support.microsoft.com/en-us/office/lookup-function-446d94af- 663b-451d-8251-369d5e3864cb Page 36 of 63 All Rights Reserved. Vol. TLE001/03-2022