ASSIGNMENT – 29

Use of Formula – HLOOKUP

Topic: Horizontal Lookup Using HLOOKUP

HLOOKUP is used to search for a value in the first row of a table and return a related value from a specified row.

TABLE 1 – Monthly Sales

Enter the following data:

MONTH JANUARY FEBRUARY MARCH APRIL MAY JUNE
SALES 325 280 450 520 675 740

Questions

Q.1 Find the Sales of April using HLOOKUP.

Q.2 Find the Sales of June using HLOOKUP.

TABLE 2 – Student Marks

Enter the following data:

NAME Aarav Riya Karan Neha Mohit Simran
ENGLISH 78 85 72 91 64 88
MATHS 82 76 89 95 70 84
SCIENCE 75 90 81 87 68 92

Questions

Q.3 Find the English marks of Neha using HLOOKUP.

Q.4 Find the Maths marks of Karan using HLOOKUP.

Q.5 Find the Science marks of Simran using HLOOKUP.

Q.6 Find the English marks of Aarav using HLOOKUP.

TABLE 3 – Employee Sales

Enter the following data:

EMP ID EMP1001 EMP1002 EMP1003 EMP1004 EMP1005
SALES 1 65 48 72 85 91
SALES 2 54 76 68 92 80
SALES 3 70 83 75 88 95
SALES 4 62 79 81 96 87

Questions

Q.7 Find Sales 4 of Employee ID EMP1004 using HLOOKUP.

Q.8 Find Sales 2 of Employee ID EMP1002 using HLOOKUP.

TABLE 4 – Temperature & City

Enter the following data:

TEMPERATURE (°C) 22 28 34 39 45
CITY Delhi Jaipur Mumbai Pune Bengaluru

Questions

Q.9 Find the City where the temperature is 39°C using HLOOKUP.

Q.10 Find the City where the temperature is 45°C using HLOOKUP.

Formula Examples

Table 1

If the table is in A1:G2:

April Sales

=HLOOKUP("APRIL",A1:G2,2,FALSE)

June Sales

=HLOOKUP("JUNE",A1:G2,2,FALSE)

Table 2

If the table is in A1:G4:

Neha’s English Marks

=HLOOKUP("Neha",A1:G4,2,FALSE)

Karan’s Maths Marks

=HLOOKUP("Karan",A1:G4,3,FALSE)

Simran’s Science Marks

=HLOOKUP("Simran",A1:G4,4,FALSE)

Table 3

If the table is in A1:F5:

EMP1004 – Sales 4

=HLOOKUP("EMP1004",A1:F5,5,FALSE)

EMP1002 – Sales 2

=HLOOKUP("EMP1002",A1:F5,3,FALSE)

Table 4

If the table is in A1:F2:

Temperature 39°C

=HLOOKUP(39,A1:F2,2,FALSE)

Temperature 45°C

=HLOOKUP(45,A1:F2,2,FALSE)

Important Point About HLOOKUP

The syntax of HLOOKUP is:

=HLOOKUP(lookup_value,table_array,row_index_num,FALSE)

Meaning:

Argument Meaning
lookup_value Value you want to search
table_array Complete table
row_index_num Row number from which result is returned
FALSE Exact Match

Example:

=HLOOKUP("EMP1004",A1:F5,5,FALSE)

Excel will:

EMP1004 → Search in the first row → Move to row 5 → Return the Sales 4 value.

Skills Covered

Students will practice:

  • HLOOKUP basics

  • Horizontal lookup

  • Exact Match using FALSE

  • Using row index numbers

  • Finding monthly sales

  • Finding student marks

  • Finding employee sales

  • Finding city from temperature

  • Using HLOOKUP with text values

  • Using HLOOKUP with numeric values

PreviousNext