How to use MATCH function in Excel 2013

How to use INDEX function in Excel 2013

How to use CHOOSE function in Excel 2013

Excel's Lookup functions like VLOOKUP, HLOOKUP, CHOOSE, INDEX, MATCH etc. are used to find and return data from a list or table based on a lookup value or index number.

In CHOOSE function, it uses an index number to find and return a specific value from a corresponding list of data. The index number indicates the position of the value in the list.

Syntax:     CHOOSE(index_num, value1, [value2], [value3]....[value254])

• index_num: Specifies the Value is to be returned by the function. It could be a number or formula or a cell reference. The value for this should be betweent 1 and 254.
• value1: The value/data we want to fetch when "index_num" equals 1
• value2: The value/data we want to fetch when "index_num" equals 2

The value/data for output { value1, value2, .....etc.} could be numbers, cell references, defined names, formulas, functions, or text.

IMPORTANT NOTES:

• If index_num < 1 OR if index_num > "number of values" then Excel gives #VALUE! error
• If index_num is a fraction, it is changed to the lowest integer before being used (eg. 3.8 to 3)
• If index_num is an array, every value is evaluated when CHOOSE is evaluated

How to use VLOOKUP in Excel 2013

VLOOKUP is probably one of the most useful function in Excel when it comes to looking up fields related to a variable. We can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.

The V in VLOOKUP stands for Vertical i.e. it looks up in vertical table. Since most of the real world data appears in vertical table, the VLOOKUP is one of the most used function in Excel. It works similar to human mind, first it searches for relevant key item and goes further in the row to get it's details.

Let's understand this better by most commonly encountered real world example of Employee details. Suppose a company has 10 employees with unique Employee ID followed by details like Name, Departement, Contact Number and E-mail ID.

If we need the Contact Number corresponding to Employee ID 201309 we first search the row in which this ID exists and then we move further in the same row to locate Contact Number. That's exactly how Excel also works, we just need to feed the data for what we are looking and from where we need to look.

Syntax:      VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: Insert the value to search in the first column of range or table. This value could be a value or it could be a cell reference (more dynamic). This helps excel to identify the row in which our data exists.
• table_array: The entire range of cells that contains data. We can directly use the range reference or a range name. This tells excel the location for search.
• col_index_num: The column in range selected under table_array corresponding to desired matching value field. (eg. Contact Number lies in column 4, hence 4 should be inserted)
• range_lookup: Optional field. Specifies whether we need exact match or approximate match. FALSE (or 0) corresponds to exact match while TRUE (or 1) corresponds to approximate match. The default value is FALSE (or 0) i.e. exact match.

IMPORTANT NOTES:

• If col_index_num < 1, Excel gives #VALUE! error
• If col_index_num > number of columns in range, Excel gives #REF! error
• While searching for text values in the first column, ensure that any cell value does not contain leading spaces, trailing spaces or inconsistent quotation marks. It may give error or incorrect values in the output.
• While searching for number or date values in the first column, ensure that data is stored in appropriate format (not as text format)