Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Excel HLOOKUP Function
2.1.
EXAMPLES
2.2.
Points to Note
3.
FAQs
4.
Key Takeaways
Last Updated: Mar 27, 2024
Easy

HLOOKUP Function

Author Komal Shaw
1 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

The HLOOKUP function is used to find and retrieve a value from data in a horizontal table.

The “H” in the function name stands for horizontal. Thus this means Horizontal Lookup. The lookup values must appear in the first row of the table and move horizontally to the right.

Excel HLOOKUP Function

The purpose of this function is to look for a value in a table arranged horizontally.

The return value is the matched value from the table.

SYNTAX -

“=HLOOKUP (lookup_value, table_array, row_index, [range_lookup])”

ARGUMENTS -

lookup_value refers to the value that we want to look up.

table_array is the table from which we want to retrieve the value/data.

row_index is the row from which we want to retrieve the data.

range_lookup is optional. It is a boolean value that indicates an exact match or approximate match. (TRUE is the default value which refers to the approximate match)

EXAMPLES

Approximate Match

Source

The sales amount is in C5:C13, and the lookup table is H4:J6. Our goal is to find the best match and not an exact match(for each value in C5:C13). To get the level and bonus, we need to change the row_index. The range_lookup will be set to 1, which will refer to the approximate match.

  1. =HLOOKUP(C5, table, 2, 1)
    //this will get the level.
  2. =HLOOKUP(C5, table, 3, 1)
    //this will get the bonus
     

Exact Match

Source

This is an example of an Exact match. Here we will be looking up the correct level for a numeric string 1-4. The table is G4:J5. The range_lookup will be set to FALSE for an exact match.

  1. =HLOOKUP(C5, table, 2, FALSE)
    //exact match

Points to Note

  1. When the range_lookup is set to False, it requires an exact match.
     
  2. When the range_lookup is omitted or True, and no exact match is found, the function will look for the nearest value in the table(less than the lookup value).
     
  3. When range_lookup is True, the lookup values of the first row of the table must be sorted in ascending order; else, it may return an incorrect answer. But when it is an Exact match situation, the values need not be sorted.
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

FAQs

  1. What does HLOOKUP function support?
    It supports approximate and exact matching and wildcard for partial matches.
     
  2. How many arguments does HLOOKUP take?
    Four arguments.
     
  3. What are those arguments?
    lookup_value, table_array, row_index, and range_lookup.
     
  4. What type of value does range_lookup accept?
    It accepts Boolean Values.
     
  5. What does 0 and 1 mean in range_lookup?
    1 or True refers to approximate match, and 0 or False refers to exact match.

Key Takeaways

In this article, we have extensively discussed the HLOOKUP Function and how to use this function. We hope that this blog has helped you enhance your knowledge regarding the HLOOKUP Function.

If you want to learn more, check out our articles on XLOOKUP FunctionVLOOKUP FunctionDays and DATEDIF Function, and Day, Month, Year Function.

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. 

Enrol in our courses and refer to the mock test and problems available.

Take a look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow. 

Happy Coding!

Previous article
Index and Match Function
Next article
VLOOKUP Function
Live masterclass