Lookup

Hi Uto

VLOOKUP and HLOOKUP are functions in Excel that allow you to search a table of data and based on what the user has supplied and give appropriate information from that table.

If you have a table of Student ID, Student Name and Grades, you can set up Excel so that if a Student ID is supplied by the user, it will look through the table and output the student's name and grade.

VLOOKUP allows you to search a table that is set up vertically. That is, all of the data is set up in columns and each column is responsible for one kind of data. In the example above, there would be a separate column of data for Student ID, Student Name, etc.

HLOOKUP is the exact same function, but looks up data that has been formatted by rows instead of columns.

So you would use HLOOKUP if the table was set so each row represented one type of data. . In the example above, there would be a separate ROW of data for Student ID, Student Name, etc.

The format of the HLOOKUP function is:

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).

The lookup_value is the user input. This is the value that the function uses to search on. If you are searching a table by the Student ID number, then the lookup_value is the cell that contains the inputted Student ID number being looked up.

The table_array is the area of cells in which the table is located. This includes not only the row being searched on, but the data rows for which you are going to get the values that you need. Back to the example, the table_array would not only include the Student ID row, but the rows for the Student Names and Grades as well.

The row_index_num is the row of data that contains the answer that you want. If your table is set up as: row 1 - Student ID, row 2 - Student Name, row 3 - Grades and you inputted a Student ID and you want to retrieve the grade that was received for that person, the row_index_num would be 3. 3 is the row number of the data row for the ANSWER that you are trying to look up.

Range_lookup is a TRUE or FALSE value. When set to TRUE, the lookup function gives the closest match to the lookup_value without going over the lookup_value. When set to FALSE, an exact match must be found to the lookup_value or the function will return #N/A. Note, this requires that the row containing the lookup_value be formatted in ascending order.

Hope this helps

Carlos