Excel – Basic HLOOKUP along with Hindi Video

HLOOKUP FUNCTION:

Summary

HLOOKUP is one of the most powerful tools in Excel. The HLOOKUP function enables you to look up data corresponding with the row of a lookup value and corresponding row index. It sounds like a very simple thing, and it is.

For example, the HLOOKUP function can use the relational data essentials of two separate tables and affix the data from one table to the other table.

you can create a simple Excel table that uses HLOOKUP to lookup and present data from one or other tables.

Don’t worry if it’s sound little difficult, by the end of this article, you will be able to teach others about the HLOOKUP.

First will let you know about the purpose and syntax of the same.

Purpose

The HLOOKUP function can use the relational data essentials of two separate tables and affix the data from one table to the other table.

Arguments & Syntax

This function syntax contain 4 different arguments, meaning of the same is given below.

Arguments	Meaning
lookup_value	The value available in the first column of the source table (table_array) or can say the value to look for in the first column of the specified range from which retrieving value.
table_array	The table specified as a range from which to retrieve the value (table prepared row-wise).
row_index_num	The value row from which to retrieve data.
[range_lookup])	The same is optional TRUE is approximate match and FALSE is an exact match

Translation

So now we will start using a sample broking client investment data table.

This table has been organized and separated into two different worksheets and each worksheet containing a different table order, the first sheet contains column-wise data and the second sheet contains row-wise data.

First table containing Investor name, Account number, Investment scheme but the investment amount is not there.

In the second sheet, we are having Investors Name, Account Number, and Bank Balance. Investor name can be different in bank and broking house or there can be a mistake done by any one of them so we will use Account number as a lookup value.

Sheet1 = This is an Investment Sheet.

Sheet2 = This is a Bank Balance sheet that contains row-wise data.

Formula Entered Above =HLOOKUP(B3,'Bank Balance'!$A$2:$N$3,2,0)

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

Sr. No.	Argument	Value
1	lookup_value	B3
2	table_array	'Bank Balance'!$A$2:$N$3
3	row_index_num	2
4	[range_lookup])	0 or FALSE

If in the above formula value in “B3” is common in the array table (Source data) and lookup value, and select 'Bank Balance'!$A$2:$N$3 where row no. “2” contains the same value available in “B3” and thereafter mentioned returned row number as 2, so the return value is bank balance (highlighted in yellow color Rs. 619790).

Click here to download the excel file or open with Google sheet.

Click here to open ASHVI CITY – Mr. Excel youtube video in Hindi or click on the start button to view caption video.

Function Return Value

The value entered by you for TRUE or FALSE.

Functions Used

=HLOOKUP(B3,'Bank Balance'!$A$2:$N$3,2,0)

Evaluation Operator	Meaning
=	Equal To
$	To block the selected cell or range

Common Difficulties:

ERROR	Meaning of Error
#NAME? in cell	This usually means that the formula is misspelled.
#REF! in cell	This usually means that something is missing in formula or row no. selection is greater than the selected array table (source data).

Important Notes:

The important thing to remember when using HLOOKUP: 

Your data has to be organized in rows.

The selection of the source data needs to start from a common value.

Before Copy and Paste using the dollar sign. ($) to block the selection range and

Start counting for “row_index_num” from the range selected from “table_array” instead of the sheet column.

Are you are having any Question or Comment? Hit me!