Excel **HLOOKUP** function can be used when you are dealing with huge datasheet and need to look up the values of the specific record. HLOOKUP stands for Horizontal lookup, used to find specific data from the datasheet. Unlike VLOOKUP function, it looks for the values horizontally which enables the user to extract a complete record. This post elaborates how & where you can use HLOOKUP function.

Launch Excel 2010, and open a datasheet on which you want to apply HLOOKUP function.

For instance, we have included student grading sheet containing courses name fields and a field containing name of the students as shown in the screenshot below.

Now we want to apply HLOOKUP function, for this we will start off with adding a new row in which we will be looking up for the data of specific student. Suppose we are dealing with a gigantic datasheet containing loads of student records, in this scenario if we want to look up for the record of student *Jack, *then we will add the row label by the name of student and start pulling the marks he secured from each each Course column; e.g. *Software Engg, Se II* etc.

Now we will be writing formula adjacent to *Jack *cell, Unlike VLOOKUP function, it looks for data horizontally, The syntax of HLOOKUP function is;

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup] )

The first parameter of the formula *lookup_value *defines the value of the number which we will be looking in the specified row. We need to lookup the mark in the newly created cell. The next parameter, *table_array *defines the table array we will be referring to in our case it will be portion of datasheet containing courses labels, with corresponding data (courses with marks secured ). r*ow_index_num *defines, which row we want to extract values to show, in our case it is the second row that contain respective course marks. *[range_lookup] *lets you to choose an option either TRUE(approximately matching of values) or FALSE (Exact matching of values).

We will write this function in the cell adjacent to *Jack *cell, it will go like this;

=HLOOKUP(B1,$B$1:$H$11,2,FALSE)

In the formula parameters*, B1* is cell of column *Software Engg* which contain marks secured by the student Jack*, B1:H11* is the array of the data containing all courses labels and their respective values (use absolute referencing with $ sign), *2 *in the formula means that values from second row will appear, and FALSE* defines that *we need exact match.

As you can see in the screen shot below that, the formula will yield *18 *the marks secured by *Jack *in *Software Engg* course (at B1 location)

Now if you want to know marks secured by Jack in each course, just drag the plus sign across the row, as shown in the screenshot below.

There is another interesting way in which HLOOKUP function can be used, in this we will be creating exclusive table for student jack and looking up for the marks, by specifying course name, as shown in the screenshot below.

The whole formula is the same except the first parameter (A44), actually it is referring to the cell in which will write the course name. Now when we will specify course name, corresponding marks value will automatically appear.

Now apply it over the table and make an exclusive one for student *Jack.*

You can also check out our previously reviewed guides on VLOOKUP Function In Excel & How to embed videos in Excel 2010.

Pingback: Excel 2010 PMT (Payment) Function

Pingback: Find Length of Characters In Excel 2010

Pingback: Excel 2010 Currency Values

Pingback: Excel 2010: Matrix Inverse Function (MINVERSE)

Pingback: Excel 2010: INFO Function

Pingback: Excel 2010: Finding Factorial (FACT Function)

Pingback: Ranking Values In Excel 2010 With RANK function

Pingback: All About Fractions In Excel 2010

Pingback: Excel 2010: Calculate Working Days In Date Format

Pingback: MROUND Function In Excel 2010

Pingback: Excel 2010 EDATE function

Pingback: Find Correlation Between Datasets In Excel 2010