Brief Introduction
HLOOKUP stands for Horizontal Lookup
or Search and can be used to retrieve information from a table by searching a
row for the matching data and output from the corresponding column. While
VLOOKUP searches for the value in a column, HLOOKUP searches for the value in a
row.
Purpose:
Lookup a value in a table by
matching on the first row.
Syntax of HLOOKUP Function:
The syntax of HLOOKUP Function in
Excel is as follows;
=HLOOKUP (lookup_value, table_array,
row_index_num, [range_lookup])
Return value:
The matched value from a table.
Arguments:
- Value - The value to look up.
- Table - The table from which to retrieve data.
- Row_index - The row number from which to retrieve data.
- Range_lookup - [optional] this option indicate exact match or approximate match. Default = TRUE = approximate match.
Match type:
When we are using a test with HLOOKUP Function, we can use any of the following match types,
Match
Type
|
Behavior
|
Details
|
TRUE
|
Approximate Match
|
MATCH finds the smallest value
greater than or equal to the lookup value. Lookup array must be sorted in descending order.
|
FALSE
|
Exact Match
|
MATCH finds the first value
exactly equal to lookup value. Lookup array does not need to be sorted.
|
You can download free
the Complete Practice of Concern Topic of Microsoft Excel File, Click on the below
download icon,
This article explains in simple terms how
to use HLOOKUP Function. It takes a step-by-step approach, & following below
contents we will touch in this post.
- . HLOOKUP Function explanation through various examples
- Use of HLOOKUP Function to return multiple values from a single Horizontal LOOKUP
Let’s start to learn something
new about the HLOOKUP Function of Microsoft Excel.
1. HLOOKUP
Function-Example
1.
HLOOKUP Function is used to find
out the exact match or Approximate match horizontal value, as we explain this formula
through a given below example,
Explanation : - The above specific given information in "Row
2 & 3" is our criteria for given commission to the agent on the sale of specific
furniture, As by using the HLOOKUP Function, we can get our proper answer, In
the example, the selected cell "C8" show the formula that how we use
it,
In this scenario, we have use a formula like below,
=HLOOKUP(B8,$B$2:$F$3,2,TRUE)
|
As in above formula the Lookup
value is $163,678/- While index Number is "2" (it mean that from
selected table array "B2:F3", we need a return from the second row "Commision
%", if we need Sale figure then Index number maybe 1), So as the value
$163,678/- is greater than the value $150,000/- therefore the approximate match
according to our given criteria is "15%".
Example 2.
For the approximate match, we use the word "TRUE" in the formula, as in this formula we also can use any numeric number like "5, 10, 3*15" etc. As shown in below example,
Explanation: - In the above-given example we see that for
approximate match the numeric number "10" is used instead of "TRUE"
which also give the correct answer, While in cell "C11" which show "#N/A"
it mean that the value $19,790/- is less than the required criteria therefore
it shows an error, while our minimum criteria is $20,000/-.
In this scenario, we have used a formula like below,
=HLOOKUP(B10,$B$2:$F$3,2,10)
|
Example 3.
If in case we need the exact
match value then we should use in formula area range_lookup "FALSE",
as shown in the below image,
Explanation : - In the above-given example we see that for exact
match the FALSE is used instead of TRUE which also gives the exact answer, the
selected cell "C10" show the formula that how we use it.
In this scenario, we have used a formula like below,
=HLOOKUP(B10,$B$2:$F$3,2,FALSE)
|
As in Cell "B10" the
value is $150,000/- so according to our criteria "15%" is the exact
answer. However, the cell "B8" contain the value of $163,678/- not
meet our criteria so its output is an error (#N/A).
Example 4.
By using above same information or data this time we use text ("Commision %" or "any other word") instead of a Lookup value, see in the below example.
Explanation: - As according
to the given formula in cell "B10", the text or word "commission
%" is used & the row in which we want to result is "4", so
according to our selected criteria, the row "4" name is "Bed"
& their exact match result is "15%".
In this scenario, we have used a formula like
below,
=HLOOKUP("Commision
%",A2:D7,4,FALSE)
|
2. Use of HLOOKUP Function to return multiple values
from a single Horizontal LOOKUP.
HLOOKUP Function most powerful use is to return multiple values from a single Horizontal lookup, just see in below example,
Explanation: - This the formula has few technical steps which you should know before applying such a
formula. Steps are as Follow,
Step 1: - We should first select the required
range of row in which we want to put the formula.
Step 2: - After typing "TRUE", "FALSE"
& "any numeric Number", we need to press "Ctrl + Shift + Enter" instead of the "Enter
key", because "Ctrl + Shift + Enter" will enclose the HLOOKUP
formula in curly brackets. As shown in the above example, all cells will give the results in one go. We will be saved from having to type the formula in each cell.
|
In this scenario we have use a formula like below,
{=HLOOKUP("Sale",A2:D7,{1,2,3,4,5,6},TRUE)}
|
In
above example we used the range lookup "TRUE" but in same example now
we use "FALSE" instead of word "TRUE", See in below example,
Explanation: - As by using
the range lookup "FALSE", the result is the same, only one thing keep in
mind that we should follow the above mention steps.
In this scenario we have use a
formula like below,
{=HLOOKUP("Sale",A2:D7,{1,2,3,4,5,6},FALSE)}
|
The third test of HLOOKUP Function
when we put numeric figure instead of "TRUE" OR "FALSE",
see in below given example,
Explanation: - As by using
in the range lookup any Numeric Figure like "10, 3*15" etc. the
result will be same, only one thing keep in mind that we should follow the
above mention 2 steps.
In this scenario, we have used a formula like below,
{=HLOOKUP("Sale",A2:D7,{1,2,3,4,5,6},10)}
|
No comments:
Post a Comment