Lesson#11: How to use the HLOOKUP function in Excel?

Posted on Posted in Excel Funtions No Comments Tagged with ,
Lesson#11: How to use the HLOOKUP function in Excel?
HLOOKUP is a function that looks for a value in the top rows of a table or array and returns the value in the same column from a row you specify. It is nothing but another function for looking up values against a row like VLOOKUP is against columns.
I have made this table like the above picture. In the first column, I have put the name of entities like Roll No in the first row, Name in the second, and in the next rows, I have put subject names. Here I made columns for every student.

Below the main table, I made a small table to look up the value or marks against the subject name for any student. For time-saving I have made drop-down menu of student names by using data validation.

Structure of HLOOPKUP is HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

  • lookup_value: value to look for — can be a value or a cell reference.
  • table_array: lookup table — range reference or a range name, with 2 or more columns.
  • row_index_num: row in the lookup table, with value to be returned
  • [range_lookup]: for an exact match, use FALSE or 0; for the approximate match, use TRUE or 1, with lookup value row sorted in ascending order

Now I will put this formula =HLOOKUP(G15, $D$6:$O$12, 2, FALSE), where G15 cell is the name of the student, $D$6:$O$12 is the first row from where it will match the student name and 2 is the row number of the selected array for Language subject.

Similarly, I will put this formula =HLOOKUP(G16, $D$6:$O$12, 3, FALSE), where G16 cell is the name of the student, $D$6:$O$12 is the first row from where it will match the student name and 3 is the row number of the selected array for Math subject.

Now you can see the result. You are now able to check marks for any subject of any individual student by using the HLOOKUP function.

See also  Lesson#39: Cool tricks to make automatically updating Gantt chart

Related video tutorials:

About

Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.

Leave a Reply

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

*