We have already seen how to utilize the VLOOKUP Function in a previous tutorial. We are now going to see how to use the HLOOKUP Function. HLOOKUP stands for horizontal lookup and is used when the range at hand is organized or arranged in a horizontal manner. While vertically arranged tables are the most common tables found, one may occasionally encounter a horizontally arranged table or data set.
Read More: VLOOKUP versus INDEX and MATCH versus DGET
Table of Contents
Syntax of HLOOKUP Function
The syntax for the HLOOKUP Function is:
=HLOOKUP(lookup_value, table_array, row_index_number, [range_lookup])
where lookup_value is the reference value one wants to find. This is required.
table_array is the table or rectangular grid containing the whole data set. This is required.
row_index_number is the row number where the related or matching data is to be sourced from. The first row is numbered from 1. This argument is required.
range_lookup is an optional parameter and FALSE delivers an exact match whereas true delivers an approximate match.
So, let’s get started with a simple example to illustrate how to use the HLOOKUP Function.
Read More: How to Use the Database Functions in Excel
How to Use HLOOKUP Function in Excel
Playing tennis professionally means that one is paid to play tennis, and of course, it is the wish and hope of many tennis coaches to have their players turn pro. While pros that are not highly ranked may not earn as much as their higher ranked counterparts, it still is a prestigious achievement.
The bane of life for every tennis player and pro tennis player is tennis injuries. Common tennis injuries include tennis elbow, rotator cuff tears, stress fractures, issues with the patella, other knee related injuries and ankle sprains. Tennis injuries can result in prolonged time off the court, so there are strategies that coaches advise in order to avoid tennis related injuries.
A hypothetical tennis coach is evaluating the players he coaches in terms of injury status and whether or not the player plays on the professional circuit. He’d like to firstly use the HLOOKUP Function in order to return a specific player’s injury status and then utilize the HLOOKUP Function in order to return whether or not that specific player plays pro or not.
Read More: LEN function in Excel
The source data is shown below:
1) So first things first in cell B10, we enter the name Sally Smith as shown below.
2) In order to find her injury status in cell B11, we enter the following formula:
3) Upon pressing CTRL-ENTER a value of Not Injured is delivered.
4) Now in cell B12, we want to find out whether Sally Smith plays pro or not using the HLOOKUP Function, so we enter the following formula:
5) Upon pressing CTRL-ENTER a value of Yes is delivered.
And there you have it.
Download Working File
The HLOOKUP Function is immensely useful when data is organized in a horizontal fashion, and while this type of arrangement is not as common as vertically arranged tables, one will find horizontally arranged tables or data ranges from time to time.
Please feel free to comment and tell us if you have ever used the HLOOKUP Function in your spreadsheets.