Basic Excel Functions you should know #1 (Vlookup | Hlookup)

You would be surprised to know how much the world of Finance rely on Excel spreadsheets. From day-to-day functions to modelling and valuation exercises, there is an excel spreadsheet. It's a no-brainier that a basic knowledge of Excel and its functions is of paramount importance before you start in the industry.

So, let's take a look at some of the most used and the basic excel functions you need to know before you take upon the responsibility of a proud keyboard warrior ;) #NoPressure

---

VLOOKUP

Ah, the good old Vlookup. This function is one of the most popular Excel function.

VLookup is used to lookup and retrieve data from a specific column in a table. It searches for a value in the first column of a table or an array of values, and then returns a value in the same row from a column that you specify in the table or the array.

Use Vlookup when the comparison values are located in a column to the left of a table of data, and you want to look right a specified number of columns.

Formula – “=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)”

In simpler words the formula is “=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an exact or approx. match)

Sounds confusing? Let's make it simpler with an example. Hang on for a few minutes and i promise you will come out looking like the kid.

Let's take an example (I have added the excel file here so that you can try it along)

Here, we have a dataset from cell A2:C7

• In column A, we have the names of the individuals

• In column B, we have the position held by these individuals

• In column C, we have the salary of the individuals

Now, in cell, B9, we give an input which will be our lookup value

In cell B10, we will use our Vlookup formula to retrieve the Position of Santanu

• Let’s have a look at what the formula does:

• The lookup_value is B9 which is Santanu

• The table_array is A3:C7. It is important that we need to start our array from the column where our lookup value is located. Here column A’s index is 1, B is 2, and C is 3

• Col_index is 2, which means we are asking the formula to look in column B for value that we need to retrieve

• Range_lookup – We keep it at False to force the formula to do an Exact search

Here's how the formula works:

• It first looks for input which in our case is Santanu in the table array that we have designed, which it will find in row 5, or cell A5

• Secondly after finding the lookup value it will retrieve the value from cell B5. Note here the formula will stick to the same row and retrieve the value from the column specified

• False means Exact match. In case the formula cannot find Santanu, it will return an error

Similarly, if we change the value of column_index from 2 to 3, it will return the value from column C which has the salary amount

---

HLOOKUP

Now that we have learned VLookup, there are times when the data is not laid out in a vertical manner and you have to instead do a horizontal search. In such cases, we have something called HLookup.

This function performs a horizontal lookup by searching for a value in the top row of the table and retuning the value in the same column based on the index number

Formula – “=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)”

In simpler words the formula is “=HLOOKUP(What you want to look up, where you want to look for it, the row number in the range containing the value to return, return an exact or approx. match)

Let's take an example (I have added the excel file here so that you can try it along)

Here, we have a dataset from cell A2:N5

• In row 2, we have the Order IDs

• In row 3, we have the unit price

• In row 4, we have the quantity

• In row 5, we have the amount

Now, in cell, B8, we give an input which will be our lookup value

In cell B9, we will use our Hlookup formula to retrieve the position of #791

Here's how the formula works:

• The lookup_value is B8 which is 791

• The table_array is B2:N5. It is important that we need to start our array from the row where our lookup value is located. Here row 2 index is 1, row 3 is 2, row 4 is 3 and row 5 is 4

• Row_index is 2, which means we are asking the formula to look in row 3 for value that we need to retrieve

• Range_lookup – We keep it at False to force the formula to do an Exact search

Similarly, if we change the value of row_index from 2 to 3, it will return the value from row 4 which has the quantity

---

Let’s talk a bit more about Range_lookup

We have the option of using Exact match and Closest Match when we are looking for value

Exact match – When we are using the formula to retrieve information based on a student ID, or a Bank account number, we have to use exact match

Closest match – Sometimes, you have a table that defines ranges, for eg –

• Rs. 5,000 – Small deposit

• Rs. 20,000 – Medium deposit

• Rs. 1,00,000 – Big deposit

• Rs. 5,00,000 – Huge deposit

Now if we want the lookup function to find the description for a deposit of Rs. 23,000 (which should retrieve “Medium deposit”), we will ask it find a close match, in this case it will find Rs. 20,000

Closest match is also helpful when dealing with dates. Look at the following table:

• 4/4/2020 – Payment on time

• 6/6/2020 – Late payment with small fine

• 7/7/2020 – Late payment with big fine

Now, if we have a payment made on 6/14/2020, the function will relate to the data 6/6/2020, and retrieve “Late payment with small fine”

An excact match in such scenarios will lead to a '# N/A' error

Possible Errors and what it means:

# Ref! = If your function col_index_num is larger than the number of columns in your table_array, the lookup function will return a Ref error

# Value! = If your function col_index_num is less than 1, the lookup function will return a Value error

# N/A = If you input FALSE for your range lookup parameter and no exact match can be found, the lookup function will return a # N/A error