HLOOKUP function in Excel With examples

How to use HLOOKUP function in Excel With examples

Topics

• HLOOKUP in Excel
• Definition and Syntax of HLOOKUP in Excel
• How to Use HLOOKUP in Excel
• Syntax of HLOOKUP in Excel in Hindi
• How to Use HLOOKUP in Excel in Hindi
• Few important points about HLOOKUP Function
• Examples of Excel HLOOKUP

HLOOKUP in Excel

HLOOKUP means Horizontal Lookup and can be used to search for matching data in a row and to obtain information for output from the corresponding column. While VLOOKUP searches for values ​​in a column, HLOOKUP searches for values ​​in a row.

In HLOOKUP, “H” means “Horizontal”, where the lookup values ​​in the first row of the table rotate horizontally to the right. HLOOKUP supports wildcards (*?) To find approximate and exact matches and partial matches.

Definition and Syntax of HLOOKUP

Definition and syntax of HLOOKUP function in Excel

Microsoft Excel defines HLOOKUP as a function that “looks for a value in the top row of the table or an array of values ​​and returns the value in the same column from the row you specify”.

The purpose

See a value in a table by matching on the first row

Return value

Values ​​matched to a table.

How to Use HLOOKUP in Excel

HLOOKUP in Excel

Now, let’s figure out how to use this function in Excel.

Suppose we have a student table as shown below:

Syntax of HLOOKUP in Excel

=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

Here, ‘lookup_value’ represents a value to be searched in the top row of the table. The table array is nothing but the rows of data in which the lookup value will be searched. The table array can be a regular range or a named range or even an Excel table.

Table_array is the name of the range and range of references within which to view the data.

‘Row_index_num’ is the row number in ‘table_array’, from which the match value is to be returned. If you give a value of ‘Row_index_num’ here, 1 returns a value from the top row in table_array and likewise returns 2 in a row_index_num, then a value from the second row of table_array.

The ‘Range_lookup’ argument accepts a Boolean value specifying whether you want to return an exact match or an approximate match. TRUE is for an approximate match while FALSE is for an exact match.

How to Use HLOOKUP in Excel

use HLOOKUP in Excel

Now, let’s figure out how to use this function in Excel.

Consider, we have a student table as shown below:

Objective: In this case, our objective is to extract the mark of satisfaction in English using Horizontal Lookup.

Therefore, we will try to implement a HLOOKUP to get the result.

‘Lookup_value’: As we know that we have to find marks of satisfaction, so our ‘lookup_value’ will be a “Santosh”.

‘Table_array’: In this argument, we refer to our table ie A1: F5.

‘Row_index_num’: In this case ‘row_index_num’ would be 4 because here we have to get a value from the fourth row of the table.

‘Range_lookup’: ‘range_lookup’ will be FALSE because here we only want to get exact match values.

Few important points about HLOOKUP Function

Some important points about HLOOKUP

Horizontal Lookup cases an insensitive lookup. This means that it considers “SANTOSH” and “santosh” the same.
When using the HLOOKUP function the ‘lookup_value’ should always be in the top row of the ‘table_array’.
‘Range_lookup’ is an optional argument. If omitted, HLOOKUP takes its default value as TRUE (approximate match).

If HLOOKUP cannot find ‘lookup_value’, and you have given TRUE (approximate match) to ‘range_lookup’, it uses the largest value that is less than _ ‘lookup_value’.
Like VLOOKUP, HLOOKUP also supports only wildcard characters (eg:, * ‘,’? ‘) In the’ lookup_value ‘argument (if’ lookup_value ‘is the text).
If you have given FALSE to ‘range_lookup’ and HLOOKUP is unable to find ‘lookup_value’ in the defined range, then it returns # N / A error.
If ‘row_index_num’ is less than 1, then #VALUE! Error will come. If it is more than the number of columns in ‘table_array’, it shows a #REF error!

Examples of Excel HLOOKUP

Now, let’s look at some examples of the Horizontal Lookup function.

Example 1

Using the table below, score a student in English who has secured 75 marks in science.

We can use this formula to get results:

= HLOOKUP (75, B2: F5,3, FALSE)

Example 2

Using the table above, write a Horizontal LookUp formula to find the math marks for a student whose name starts with R.

To do this we can use the formula:

= HLOOKUP (“R *”, A1: F5,2, FALSE)

Note: In this example, we have used the “*” wild card character.

• Best Internet Browsers

• What is Python , its Meaning Features of python

• What is network topology and its Types

• RRB NTPC TYPING TEST PART-10