Use LOOKUP, one of the lookup and reference functions, when we need to look in a single row or column and find a value from the same position in a second row or
column. There are various functions in this category, but we will focus only on the most useful ones.
- VLOOKUP
- HLOOKUP
- INDEX-MATCH(INDEX-MATCH-MATCH)
- XLOOKUP
VLOOKUP and HLOOKUP
The VLOOKUP and HLOOKUP functions are the functions of Lookup category. These functions look for a specific value in the first row (HLOOKUP - (h)orizontal)
or column (VLOOKUP - (v)ertical) of a table and return any value from the same column (HLOOKUP) or row (VLOOKUP).
Let’s take an example, For VLOOKUP, we are using the Student Name field in the Student Dataset to find the Student Database to return the Student Name linked to the Dataset.
A VLOOKUP will be used if the Students' Names are in a column.
Formula =VLOOKUP (value to look up, table area, column number)
For HLOOKUP, we are using the Subjects field in the Student Dataset to find the Student Database to return the Subject linked to the Dataset. An HLOOKUP will be used if the Subjects are in a row.
Formula =HLOOKUP (value to look up, table area, row number)
Here’s, the sample table for Lookup functions:
we can see the Economics result for all students in table below.
=VLOOKUP($A$11,$A$1:$F$7,2,FALSE) = 68.
This is the argument for Rahul Singh, who earned 68 marks in Economics.
Sachin Sharma’s name is displayed on column no 3 (C1) of the databases, so Sachin
Sharma got 61.
The only difference in all HLOOKUP Functions is the column number. The argument of the formula is Column No. of a Dataset.
we can see that we have Sachin Sharma’s result for all subjects. we can see in the formula column that only the third argument is different in each formula as we need to define the row number this time, for HLOOKUP.
Index - Match
INDEX and MATCH are more advanced lookups. This is because they are incredibly flexible, we can do horizontal and vertical lookups, 2-way lookups, left lookups, case
sensitive lookups and even lookups based on multiple criteria. INDEX – MATCH is the best substitute for VLOOKUP and HLOOKUP.
First, we will learn Index and Match Functions separately for better understanding
and then we will merge both.
Index
INDEX retrieves the value at a given location in a range as per the following example.
In the previous example, we want to see the marks earned in Business Study by Pankaj Madan.
As per the previous formula, 5 is the row number (Business Study) and 4 is the column number (Pankaj Madan),
The result is 64.
Match
The MATCH function is designed to find the position of an item in a range.
the above figure shows that Business Study is on the third position of the range A2:A7. Now, we will combine INDEX-MATCH.
In the above figure, we got the Student – Pankaj Madan (Column No. 4 as per table) and we defined the rows by the match formula.
The formula is Insert INDEX and select the array. After that, we defined the Match function for a row.
Now, we will go one step further to use INDEX-MATCH-MATCH (Double Match).
As we can see in the previous example, we manually typed 4 to get Pankaj Madan’s
result. We will use another Match function to define columns as well.
In the above figure, we can see that we got the result of Student – Gitika Sharma in vertical mode.
In the above figure, we can see that we got the result of Business Study for all students in horizontal mode.
The formula is the same in both cases (vertical and horizontal).
XLOOKUP (For Excel 365 Users only)
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_
mode], [search_mode])
We want to get all students’ results for Accounts. As per the above figure, we got the result of Accounts by students.
Conclusion
Finally, we will learning the pillar concepts of lookups functions in microsoft excel and we also understanding how it works and which lookup function is important for us, when we doing any calculation in accounting and finance. This functions also help us for making complex calculation depending on the nature of our data and provides a better results in a financial manner.
