Practical 12 – Mastering HLOOKUP

1. Objective

The primary objective of this practical is to enable students to efficiently retrieve specific information from datasets where data is organized horizontally, using Excel’s HLOOKUP function. Students will learn to:

·         Understand the syntax and arguments of the HLOOKUP function.

·         Perform exact matches to retrieve precise data.

·         Perform approximate matches for looking up values within ranges.

·         Handle common HLOOKUP errors, such as #N/A, using IFERROR or IFNA.

·         Utilize HLOOKUP to extract data from tables structured horizontally.

·         Apply absolute references ($) effectively within HLOOKUP formulas.

2. Introduction

While VLOOKUP is used for vertical tables where lookup values are in the first column, HLOOKUP is designed for tables where the lookup values are located in the first row. It then returns a corresponding value from a specified row within the same table. This function is particularly useful when your data is structured with categories or criteria listed across the top row, and associated values listed below them. Mastering HLOOKUP complements VLOOKUP and expands your ability to retrieve information regardless of the table’s orientation.

3. Pre-requisites

·         Successful completion of Practical 3, Practical 6 (understanding of cell references), and Practical 11 (familiarity with lookup functions).

·         Basic understanding of Microsoft Excel interface.

·         Familiarity with basic formula entry.

4. Software Required

·         Microsoft Excel

5. Procedure: Practical Steps with Examples

We will use a new workbook to demonstrate HLOOKUP, setting up data in a horizontal fashion.

Step 5.1: Setting Up Data for HLOOKUP

1.     Open Excel: Launch Microsoft Excel and select Blank workbook.

2.     Rename Sheet: Rename Sheet1 to Marks Data.

3.     Enter Marks Data (on Marks Data sheet):

o    In cell A1, type Student Name.

o    From B1 to F1, type Subject 1, Subject 2, Subject 3, Subject 4, Subject 5.

o    In G1, type Total Marks.

o    Populate with some sample data (similar to Practical 3, but we’ll focus on looking up subjects).

Student Name	Subject 1	Subject 2	Subject 3	Subject 4	Subject 5	Total Marks
Alice	85	72	90	68	75	390
Bob	55	60	48	50	62	275
Charlie	92	88	95	80	89	444
Diana	30	45	38	25	40	178
Eva	70	65	72	78	63	348

4.     Rename Sheet2 to Lookup Tasks.

5.     Enter Lookup Tasks (on Lookup Tasks sheet):

o    In cell A1, type Student Name. In B1, type Lookup Subject. In C1, type Marks.

o    Populate with some lookup tasks:

Student Name	Lookup Subject	Marks
Alice	Subject 3
Charlie	Subject 1
Diana	Subject 5
Bob	Subject 2

Step 5.2: Basic HLOOKUP for Exact Match

We want to find the marks for a specific subject for each student from the Marks Data table.

1.     Go to Lookup Tasks sheet.

2.     Enter HLOOKUP Formula for Alice’s Subject 3 marks: In cell C2, type the following formula:
=HLOOKUP(B2,'Marks Data'!$B$1:$G$6,MATCH(A2,'Marks Data'!A:A,0),FALSE)

o    B2: This is the lookup_value (the subject name "Subject 3" that we want to find in the first row of our Marks Data table).

o    'Marks Data'!$B$1:$G$6: This is the table_array (the range where we want to search). We are searching in rows 1 to 6 and columns B to G of the Marks Data sheet.

§  Important: The lookup_value must be in the first row of your table_array (row 1 in this case).

§  We use absolute references ($) for the table_array to keep it fixed when copying the formula.

o    MATCH(A2,'Marks Data'!A:A,0): This is the row_index_num. HLOOKUP requires the row number from which to retrieve the value. Instead of hardcoding (e.g., 2 for Alice), we use MATCH to dynamically find the row number for the Student Name in A2.

§  MATCH(A2, 'Marks Data'!A:A, 0) searches for "Alice" (value in A2) in column A of the Marks Data sheet and returns its relative position. If Alice is in row 2, MATCH will return 2.

o    FALSE: This is the range_lookup argument. FALSE (or 0) specifies an exact match. Excel will only find "Subject 3" and not "Subject 3.5".

3.     Press Enter. Alice’s marks for Subject 3 ("90") should appear.

4.     Copy Down: Drag the fill handle from C2 down to C5 to apply the formula to all tasks.

o    Observation: You should see the correct marks for each student and subject combination.

Step 5.3: Handling #N/A Errors with IFERROR

If a Lookup Subject or Student Name is not found, HLOOKUP (or MATCH) might return an #N/A error. We can use IFERROR to make this more user-friendly.

1.     Modify Formula for Alice: In cell C2 (on Lookup Tasks sheet), edit the formula:
=IFERROR(HLOOKUP(B2,'Marks Data'!$B$1:$G$6,MATCH(A2,'Marks Data'!A:A,0),FALSE),"Data Not Found")

o    Explanation: If the HLOOKUP (or MATCH) returns an error, IFERROR will display "Data Not Found" instead.

2.     Copy Down: Drag the fill handle from C2 down to C5.

o    Observation: If you change Lookup Subject to something not in Marks Data‘s first row (e.g., "Subject 6"), you will now see "Data Not Found".

Step 5.4: HLOOKUP for Approximate Match

An approximate match is useful when your lookup_value falls within a range in the first row. For example, to find a bonus percentage based on total marks.

1.     Rename a New Sheet to Bonus Scale.

2.     Create a Bonus Scale Table:

o    In cell A1, type Min Total Marks.

o    From B1 to F1, enter 0, 150, 250, 350, 450.

o    In cell A2, type Bonus Percentage.

o    From B2 to F2, enter 0%, 2%, 5%, 7%, 10%.

Min Total Marks	0	150	250	350	450
Bonus Percentage	0%	2%	5%	7%	10%

3.     Go to Marks Data sheet.

4.     Add Header: In cell H1, type Bonus %.

5.     Enter HLOOKUP Formula: In cell H2, type the following formula:
=HLOOKUP(G2,'Bonus Scale'!$B$1:$F$2,2,TRUE)

o    G2: This is the lookup_value (Alice’s Total Marks).

o    'Bonus Scale'!$B$1:$F$2: This is the table_array (our bonus scale table).

o    2: This is the row_index_num (Bonus Percentage is in the 2nd row of our table_array).

o    TRUE: This specifies an approximate match. Excel finds the largest value in the first row that is less than or equal to the lookup_value.

6.     Copy Down: Drag the fill handle from H2 down to H6.

o    Observation: Bonus percentages should be assigned based on the total marks and the bonus scale. For example, a student with 178 total marks should get 2%, while a student with 444 should get 7%.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks:

1.     Student Performance Review:

o    On a new sheet named Performance Review, list Student Names in column A (e.g., Alice, Bob, etc.).

o    In B1, type Subject to Review. In B2, enter Subject 4.

o    In column C, use HLOOKUP to find the marks of the Subject to Review for each student. The lookup_value will be B2 (fixed with $), and the row_index_num will be dynamic using MATCH.

2.     Pass Criteria Lookup:

o    On a new sheet named Pass Criteria, create a horizontal table:

o    Row 1: Grade, A, B, C

o    Row 2: Min Pass %, 80%, 60%, 45%

o    On your Marks Data sheet, add a new column Required Pass % for Grade. Use HLOOKUP to find the Min Pass % for each student’s Grade (from your Grade column in Practical 3’s marksheet, or assume it’s I2 if added back). Use TRUE for approximate match if you want to find the min % for a range, or FALSE for an exact match to the letter grade.

3.     Subject Difficulty Rating:

o    On a new sheet Subject Ratings, create a horizontal table:

o    Row 1: Subject 1, Subject 2, Subject 3, Subject 4, Subject 5

o    Row 2: Difficulty Rating, 8, 6, 9, 7, 5

o    On your Marks Data sheet, add a new column Subject 3 Difficulty. Use HLOOKUP to retrieve the Difficulty Rating for Subject 3.

4.     Team Performance Bonus:

o    Imagine you have Team A, Team B, Team C.

o    You have a horizontal table for bonus percentages based on Team Average Score:

o    Row 1: Min Team Avg Score, 0, 50, 70, 90

o    Row 2: Team Bonus %, 0%, 1%, 3%, 5%

o    If you had a column for Team Average Score on your Marks Data sheet, use HLOOKUP with TRUE for approximate match to assign Team Bonus %.

7. Assessment / Deliverables

·         Submit the completed Excel workbook (.xlsx file) with the Marks Data, Lookup Tasks, Bonus Scale sheets, and any new sheets created for the exercises.

·         Ensure all HLOOKUP formulas are correctly implemented for both exact and approximate matches.

·         Demonstrate proper error handling for #N/A results.

·         Verify that absolute references are used appropriately where necessary, especially for the table_array.

·         Be prepared to explain each argument of the HLOOKUP function and when to use exact vs. approximate match.