Full Guide: The Logic Behind Hlookup Function Excel


Now now now! After the vertical finding of values let’s try the horizontal one. Hlookup for Horizontal Look up meaning by column. This function is same logic with Vlookup function except for lining up of finding values.

Vertical Finding – North to South

Horizontal Finding – West to East

This Picture explains the vertical line and horizontal line of how computer finds individual values in all cells
NEWS

The Formula of HLOOKUP

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

Like VLookup, Hlookup starts from most left and upper corner of the table array (or area)

Finding your value by column (HORIZONTAL LOOK UP

The computer will start finding your value on the upper-left corner of your desired area or table array (range D5:K6). Starts with the “Name” next is “Peter”, “Paul”, “John”, and so on then after the computer found your value (“Christian”), the searching ceases. It will match up to your index number from the same column which is “2”, generating a result of ID No. 000-0005.

Common Errors

  • Always look the “lookup_value” from the 1st row of the table array
  • Make sure you write the correct col_index_num (start counting from the  1st row of the table array)
  • When there are duplicate Values in your 1st row in a table array, the first value that matches the “lookup_value” is always the basis for the outcome value of your row_index_num

Always look the “lookup_value” from the 1st row of the table array:

Hlookup Common Error 1

Make sure you write the correct col_index_num (start counting from the  1st row of the table array or table area):

Which is true, because the table array starts from cell D6 where the computer is always start indexing from the first cell of a table array but on the other hand (index no.) is false. Obviously, the computer thinks that your “result_value” is on row 12 so the result found  is “#REF!”

When there are duplicate Values in your 1st row in a table array, the first value that matches the “lookup_value” is always the basis for the outcome value of your row_index_num:

If you have questions, reactions and or suggestions. Please leave a comment 🙂

Don’t forget to subscribe or follow me on google + for more future posts


If you’d like my tutorial hit this post a PLUS! 🙂

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s