ASSIGNMENT – 17

Use of Formula – HLOOKUP

Product Master Table

Enter the following data horizontally in Excel.

  301 302 303 304 305 306 307 308
ID 301 302 303 304 305 306 307 308
Brand Dell HP Lenovo Samsung Logitech Canon Acer Asus
Product Laptop Printer Computer Monitor Keyboard Scanner Mouse Tablet
Price ₹60,000 ₹15,000 ₹45,000 ₹20,000 ₹1,300 ₹10,000 ₹900 ₹25,000

Note: The first row containing the IDs is the lookup row for HLOOKUP.

Search Table

Use HLOOKUP to fill in the blank columns.

ID Product Brand Price
304      
307      
301      
305      
308      
302      
306      
303      
307      
301      

Questions

Q.1

Using HLOOKUP, find the Product of ID 304.

Q.2

Find the Brand of ID 307.

Q.3

Find the Price of ID 301.

Q.4

Find the Product and Brand of ID 305.

Q.5

Find the complete details of ID 308.

Find:

  • Product

  • Brand

  • Price

Q.6

Find the Price of ID 302.

Q.7

Find the Brand and Product of ID 306.

Q.8

Find the complete details of ID 303.

Find:

  • Product

  • Brand

  • Price

Q.9

Find the Product, Brand and Price of ID 307.

Q.10

Find the complete details of ID 301 using HLOOKUP.

Find:

  • Product

  • Brand

  • Price

HLOOKUP Syntax

=HLOOKUP(lookup_value,table_array,row_index_num,FALSE)

Example

To find the Product of ID 304:

=HLOOKUP(304,B1:I4,3,FALSE)

To find the Brand of ID 304:

=HLOOKUP(304,B1:I4,2,FALSE)

To find the Price of ID 304:

=HLOOKUP(304,B1:I4,4,FALSE)

HLOOKUP Row Numbers

Row Number Information
1 ID
2 Brand
3 Product
4 Price

Important Concept

HLOOKUP = Horizontal Lookup

It searches for the ID horizontally across the first row and then returns information from the required row.

For example:

=HLOOKUP(307,B1:I4,3,FALSE)

  • 307 → Lookup ID

  • B1:I4 → Product Master Table

  • 3 → Return Product from row 3

  • FALSE → Exact Match

PreviousNext