Advance Excel - Lookup and Reference Functions

Look up Functions

Subscribe to my INDEX & MATCH V/s LOOKUP Function Channel

In Excel everyone works on large or small size of data, at times we need to get the relevant values or cross check the data. LOOKUP in-built function in excel is versatile and commonly used for perform such tasks. We will learn the syntax of all lookup functions available in excel with the help of few examples.

MATCH Function -

INDEX Function -

INDEX & MATCH Function - Together works like VLOOKUP function. INDEX function indexes the array and matched the index of lookup value, if index number matched then we get expected result. Using both functions together is a best alternative to VLOOKUP functions.

VLOOKUP Function - Search for the value having in the data set formed vertically. V stands for Vertical lookup. VLOOKUP Function always looks up for look up value in and returns corresponding value from another column to the right side.

Vlookup Syntax contents

Lookup_value: The reference value that you want to look for relevant information in the table range.

Lookup_array: Define reference of table range to search relevant information mentioned lookup_value. Also known as Array

Col_index_num: The column number in supplied array, from where you will get the information.

[range_lookup]: This is an optional argument states as TRUE or FALSE. To use this argument, the table must have organized data set in Ascending Order.

Example:

Below table has 4 columns data and we want the fetch the department name that is in 4^th column.

LOOKUP Function - Look up function looks for relevant value to Lookup_value in a row or a column.

Lookup_value: Reference Value that looks up for value in Look up vector. Look up Value can be either Number or Text.

Lookup_vector: Is an array/range that contains single row or column.

Result_vector: Is a range that contains single row or column can be of same size of range as Lookup vector.

HLOOKUP Function - Hlookup is used to perform lookup for value from the data set organized horizontally.

‘lookup_value’ refers to a value that is to be searched in the topmost row of the table. ‘lookup_value’ can be a value, a reference or a text string.

‘table_array’ is the range reference or range name of an array of values, inside which in which the data is to be looked up.

‘row_index_num’ is the row number in the ‘table_array’, from which the matching value is to be returned. A ‘row_index_num’ equal to 1 returns a value from the topmost row in the ‘table_array’ and similarly a ‘row_index_num’ equal to 2 returns a value from the second row of the ‘table_array’.

‘range_lookup’ argument accepts a Boolean value that specifies whether you want Horizontal Lookup function to return an exact match or an approximate match. TRUE stands for approximate match while FALSE stands for an exact match.

In above examples Hlookup performs the search for Hemangi’ s marks and returns the values based upon rows number mentioned in Array/Range supplied in formula. Even using ROW() function we can get rid of changing the row_index_num.