Lookup Functions In Excel – MS Excel Functions Tutorial Part 7

We learnt Mathematical Functions, Text Functions, Cleanup and Case Functions and Logical Functions in Excel and how to use these functions. In this post, we will learn about Lookup functions in excel.

Lookup functions are used for searching for values in a list.

Function # 1 VLOOKUP and HLOOKUP

These functions are used for searching values or other information stored in the form of tables. Let us assume that we have a small student database which contains the names of students along with the marks scored by them. Now let us suppose that we are interested in finding out the marks scored by a particular student say “Julie”. This function is very useful if our dataset has hundreds of records.

VLOOKUP has the following syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

  • Lookup_value is the value which we are searching for. In this case it is “Julie”.
    Table_array is the entire cell range or table
  • Col_index_num is the column no from which we wish to retrieve our finally. For e.g. if Julie’s marks are in the second column we will indicate 2 as the column number.
  • Range_lookup takes two values “true” and “false”. When we wish to match the lookup value exactly or if our list in unsorted we should enter false.

=VLOOKUP(“Julie”,D6:E11,2,FALSE)

This formula will return Julie’s marks.

Excel Functions Tutorial (7)Excel Functions Tutorial (6)HLOOKUP

This function is similar to the VLOOKUP function. The only difference is that it searches for values in horizontal table. If the values are laid out in the first row then the table is horizontal.

Let us look at an example to understand this concept.

Excel Functions Tutorial (5) =HLOOKUP(“Julie”,D6:D17,2,FALSE)

This formula will also return Julie’s marks. An index number “2” indicates “Marks”.

Function # 2 CHOOSE

This function is used to choose and return a value from a list of values. CHOOSE has the following syntax:

CHOOSE (index_num, value 1, value 2, value 3…)

The index number can be a numerical value or a cell reference which contains a numerical value. Text values can’t be entered. Value1,2 and 3  can be numerical as well as text. These values tell excel to perform specific actions if the value is from 1 to 3. These values can range up to 256.

Sometimes the CHOOSE function is used in place of an IF function.

Let us suppose that we have a small student database and we have to perform these actions and update the status:

  • Rank 1: Reward the students
  • Rank 2: Reward the students
  • Rank 3: Issue letter of honor
  • Rank 4: Issue letter of honor
  • Rank 5: Issue letter of appreciation
  • Rank 6: Issue letter of appreciation

Let us write our CHOOSE function:

=CHOOSE(E6,”Reward”,”Reward”,”Letter of honour”,”Letter of honour”,”Appreciation letter”,”Appreciation letter”)

Excel Functions Tutorial (4)When this function is copied from cells F6 to F11 we get the following result.

Excel Functions Tutorial (3)

Function # 3 MATCH & INDEX

The MATCH function is used to find the position an item in a list of items. Let us suppose that our student database contains a list of marks scored and the highest marks is 90. Now using MATCH we can find out the position of the item 90 in the list.

=MATCH(90,E6:E11,0)

This formula returns the position of 90 in the list. Since 90 is the very first item it return.

Excel Functions Tutorial (2)This formula is very useful for finding the position of an item in a large list. The MATCH function is commonly used with the INDEX function

INDEX

This function finds the item at the intersection of a row and column. Given the position of an item we can find the item using INDEX.

Often MATCH is used to find the position of an item and INDEX is used to find out the name of the ITEM at that position.

Let us consider our previous example. Let us assume that after finding out the highest marks 90 at position 4 using MATCH we need to find out the name of the student who scored the highest marks. We can use INDEX as follows:

=INDEX(D6:D11,MATCH(90,E6:E11,0))

This formula tells excel to return the name of the item corresponding to position 1(table row 1) across the cells D6 to D11. It returns “John”. Thus now we know that “John” is our top scorer.

Excel Functions Tutorial (1)

So in this post we learnt the most useful lookup functions in excel. In next post we will learn about Date & Time Functions.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">