Lesson 12: Lookup & Reference Functions in Excel

How to use the Lookup and references formulas in Microsoft Excel?

Microsoft excel provides its users with many sets of formulas and functions such as HLOOKUP, INDEX, MATCH, OFFSET, INDIRECT and many others to make working with excel very smooth and easy.

Benefits of various formulas –

• VLOOKUP – VLOOKUP is the most commonly used function. VLOOKUP is used when users have to look-up a certain value and to extract a corresponding value in some other column. This function takes four values as it input.
• HLOOKUP – compared to VLOOKUP and INDEX function, the HLOOKUP function is not that widely used but is a useful function. This function also takes four input values. The processing takes place on the four inputs similar to that of VLOOKUP and the output is displayed.
• INDEX – INDEX function is of great use when the data is large in quantity but the user knows about the location of the data that the user wants.
• MATCH – MATCH function can be used when the user has a huge set of data and wants to identify the position of the magic data set. This function takes three arguments as its input.
• OFFSET – OFFSET function is a very volatile function. When this function is applied on large data set, it tends to slow down the worksheet speed. This function takes five arguments as its input.
• INDIRECT – INDIRECT function is again a very volatile function as it recalculates the entire worksheet whenever there’s a change in data. This tends to slow down the speed of worksheet and takes only two arguments as its input.

How to use the VLOOKUP function?

VLOOKUP function is the most widely used function. This function is used to extract a corresponding value to some other column. To learn how to work with this function, follow the following mentioned steps –

• Start by typing equals to symbol followed by the function name VLOOKUP. Inside the brackets fill in the four inputs required which are lookup value, table array, col index number and range lookup
• In the lookup array, make sure to enter the text or data you’re looking up for. It must be from the left most column of the data only.
• In the table array, select the entire table where the data is stored and those cell reference will be copied in place of table
• In the col index number, enter the column from where the user wants to return it’s data.
• In the range look up, type zero to get the exact value as the output.
• When you’ve entered the accurate data, press ctrl with enter and the output will be displayed accordingly

How to use the INDEX function?

There are two ways of using the index function. First where the number of inputs is three and the other where the number of inputs is four. To learn how to work with them, follow the following mentioned steps –

• start by typing equals to sign followed by the function name INDEX
• Inside the bracket fill in the array input by selecting the entire data set. Following the Array number, fill in the row and column number of the cell
• Press ctrl along with enter. The output so displayed will be from the rows and column address entered.
• The other way of using the index function is by using the reference input option. The reference can be more than one array
• Type the required number of array references and close the bracket. Type the row and column number and mention the area number. Press ctrl with enter to display the output.

How to use the INDIRECT function?

Indirect function is a very volatile function as it tends to slow down the pace of the entire worksheet when even a slightest change is done to the data. This function tends to recalculate the entire worksheet. To learn how this function works, follow the following mentioned steps –

• Start by typing the equals to sign followed by the function name. Inside the brackets enter the two arguments, first one being the reference text and other one the format of the reference
• The reference text must always be typed within quotes. Type the cell address in the quotes and press enter to get the value in that cell as your output.
• INDIRECT formula can be used to refer a cell which in return has a cell reference stored in it already
• Start by typing the formula followed by the cell reference which in return has another cell reference in it.
• This function Will first reach the destination mentioned within the brackets from where it will go to the next cell reference stored in it and then finally show the output.

Learn Microsoft Excel course in Singapore!

Microsoft excel is the most powerful and useful tool in the modern business world today. To equip yourself with the finest knowledge, join a best excel course in Singapore today!

If you want to become masters in Excel, Lookup and Reference formulas you must learn about. You don’t need to know each and every formula, but a basic understanding of these will help you a lot.

In this Tutorial, you will learn about the following LOOKUP and REFERENCE formulas:

• VLOOKUP function
• HLOOKUP function
• INDEX function
• MATCH function
• OFFSET function
• INDIRECT function
• ROW function
• ROWS function
• COLUMN function
• COLUMNS function

This video is a part of Excel Training Singapore, where I show you how to use Lookup and references formulas in excel from the basics and also cover a lot of advanced Features.