Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
Module 8: MS Excel Formulas and Functions
1. VLOOK UP
Use VLOOKUP when you need to find things in a table or a range by row. For example, look up a
price of an automotive part by the part number, or find an employee name based on their employee
ID.
In its simplest form, the VLOOKUP function says:
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the
range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE,
or 0/FALSE).
Tips:
▪ The secret to VLOOKUP is to organize your data so that the value you look up (Fruit) is to the left
of the return value (Amount) you want to find.
▪ If you're a Microsoft Copilot subscriber Copilot can make it even easier to insert and use VLookup
or XLookup functions. See Copilot makes lookups in Excel easy.
How to get started
There are four pieces of information that you will need in order to build the VLOOKUP syntax:
1. The value you want to look up, also called the lookup value.
2. The range where the lookup value is located. Remember that the lookup value should always
be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value
is in cell C2 then your range should start with C.
3. The column number in the range that contains the return value. For example, if you specify
B2:D11 as the range, you should count B as the first column, C as the second, and so on.
4. Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an
exact match of the return value. If you don't specify anything, the default value will always be TRUE
or approximate match.
Now put all of the above together as follows:
=VLOOKUP(lookup value, range containing the lookup value, the column number in the range
containing the return value, Approximate match (TRUE) or Exact match (FALSE)).
Examples
Here are a few examples of VLOOKUP:
Example 1
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
Example 2
Example 3
Example 4
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
Example 5
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
2. HLOOK UP
Searches for a value in the top row of a table or an array of values, and then returns a value in the
same column from a row you specify in the table or array. Use HLOOKUP when your comparison
values are located in a row across the top of a table of data, and you want to look down a specified
number of rows. Use VLOOKUP when your comparison values are located in a column to the left
of the data you want to find.
The H in HLOOKUP stands for "Horizontal."
Formula
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
=HLOOKUP(value to look up, table area, row number)
The HLOOKUP function syntax has the following arguments:
▪ Lookup_value Required. The value to be found in the first row of the table. Lookup_value can
be a value, a reference, or a text string.
▪ Table_array Required. A table of information in which data is looked up. Use a reference to a
range or a range name.
▪ The values in the first row of table_array can be text, numbers, or logical values.
▪ If range_lookup is TRUE, the values in the first row of table_array must be placed in ascending
order: ...-2, -1, 0, 1, 2,... , A-Z, FALSE, TRUE; otherwise, HLOOKUP may not give the correct value.
If range_lookup is FALSE, table_array does not need to be sorted.
▪ Uppercase and lowercase text are equivalent.
▪ Sort the values in ascending order, left to right. For more information, see Sort data in a range
or table.
▪ Row_index_num Required. The row number in table_array from which the matching value will
be returned. A row_index_num of 1 returns the first row value in table_array, a row_index_num of
2 returns the second row value in table_array, and so on. If row_index_num is less than 1,
HLOOKUP returns the #VALUE! error value; if row_index_num is greater than the number of rows
on table_array, HLOOKUP returns the #REF! error value.
▪ Range_lookup Optional. A logical value that specifies whether you want HLOOKUP to find an
exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In
other words, if an exact match is not found, the next largest value that is less than lookup_value is
returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is
returned.
▪ If HLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is
less than lookup_value.
▪ If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns
the #N/A error value.
▪ If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters, question
mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an
asterisk matches any sequence of characters. If you want to find an actual question mark or
asterisk, type a tilde (~) before the character.
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
Example
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For
formulas to show results, select them, press F2, and then press Enter. If you need to, you can
adjust the column widths to see all the data.
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
HLOOK UP Example.
Let us consider the example below. The marks of four subjects for five students are as follows:
Now, if our objective is to fetch the marks of student D in Management, we can use HLOOKUP as
follows:
HLOOKUP function in Excel comes with the following arguments:
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
As you can see in the screenshot above, we need to give the lookup_value first. Here, it would be
student D as we need to find his marks in Management. Now, remember that lookup_value can be a
cell reference or a text string, or it can be a numerical value as well. In our example, it would be student
name as shown below:
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
The next step would be to give the table array. Table array is nothing but rows of data in which the
lookup value would be searched. Table array can be a regular range or a named range, or even an
Excel table. Here, we will give row A1:F5 as the reference.
Next, we would define ‘row_index_num,’ which is the row number in the table_array from where the
value would be returned. In this case, it would be 4, as we are fetching the value from the fourth row of
the given table.
Suppose, if we require marks in Economics, then we would put row_index_num as 3.
The next is range_lookup. It makes HLOOKUP search for exact or approximate value. As we are
looking out for an exact value, it would be False.
The result would be 72.
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
Here, HLOOKUP is searching for a particular value in the table and returning an exact or approximate
value.
Important points to keep in mind about HLOOKUP
1. It is a case-insensitive lookup. It will consider, for example, “TIM” and “tim” as the same.
2. The ‘Lookup_value’ should be the topmost row of the ‘table_array’ when we are using
HLOOKUP. If we need to look somewhere else, then we must use another Excel formula.
3. HLOOKUP supports wildcard characters such as ‘*’ or ‘?’ in the ‘lookup_value’ argument (only if
‘lookup_value’ is text).
Let’s understand this using an example.
Suppose we are given names of student and marks below:
If we need to use the Horizontal Lookup formula to find the Math marks of a student whose name starts
with a ‘D,’ the formula will be:
The wild character used is ‘*’.
4. #N/A error – It would be returned by HLOOKUP if ‘range_lookup’ is FALSE and the HLOOKUP
function is unable to find the ‘lookup_value’ in the given range. We can embed the function in IFERROR
and display our own message, for example: =IFERROR(HLOOKUP(A4, A1:I2, 2, FALSE), “No value
found”).
5. If the ‘row_index_num’ < 1, HLOOKUP would return #VALUE! error. If ‘row_index_num’ > number of
columns in ‘table_array’, then it would give #REF! error.
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
6. Remember HLOOKUP function in Excel can return only one value. This would be the first value n
that matches the lookup value. What if there are a few identical records in the table? In that scenario, it
is advisable to remove them or create a Pivot table and group them. The array formula can then be
used on the Pivot table to extract all duplicate values that are present in the lookup range.
To learn more, launch our free Excel crash course now!
HLOOKUP from another workbook or worksheet
It means giving an external reference to our HLOOKUP formula. Using the same table, the marks of
students in subject Business Finance are given in sheet 2 as follows:
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
We will use the following formula:
Then we will drag it to the remaining cells.
Use of HLOOKUP to return multiple values from a single Horizontal LOOKUP
So far, we’ve used HLOOKUP for a single value. Now, let’s use it to obtain multiple values.
As shown in the table above, if I need to extract the marks of Cathy in all subjects, then I need to use
the following formula:
If you wish to get an array, you need to select the number of cells that are equal to the number of rows
that you want HLOOKUP to return.
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
After typing FALSE, we need to press Ctrl + Shift + Enter instead of the Enter key. Why do we need to
do so?
Ctrl + Shift + Enter will enclose the HLOOKUP formula in curly brackets. As shown below, all cells will
give the results in one go. We will be saved from having to type the formula in each cell.
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
3. XLOOK UP
Use the XLOOKUP function to find things in a table or range by row. For example, look up the price
of an automotive part by the part number, or find an employee name based on their employee ID.
With XLOOKUP, you can look in one column for a search term and return a result from the same
row in another column, regardless of which side the return column is on.
Note: XLOOKUP is not available in Excel 2016 and Excel 2019. However, you may come across a
situation of using a workbook in Excel 2016 or Excel 2019 with the XLOOKUP function in it, if it was
created by someone else using a newer version of Excel.
XLOOKUP function
Applies To
Use the XLOOKUP function to find things in a table or range by row. For example, look up the price
of an automotive part by the part number, or find an employee name based on their employee ID.
With XLOOKUP, you can look in one column for a search term and return a result from the same
row in another column, regardless of which side the return column is on.
Note: XLOOKUP is not available in Excel 2016 and Excel 2019. However, you may come across a
situation of using a workbook in Excel 2016 or Excel 2019 with the XLOOKUP function in it, if it was
created by someone else using a newer version of Excel.
Pause
Current Time 0:28
/
Duration 0:44
Loaded: 100.00%
Download
Captions
Audio Track
More options
Mute
Fullscreen
Play Video
Syntax
The XLOOKUP function searches a range or an array, and then returns the item corresponding to
the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate)
match.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode])
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
Argument Description
lookup_value The value to search for
Required*
*If omitted, XLOOKUP returns blank cells it finds in lookup_array.
lookup_array The array or range to search
Required
return_array The array or range to return
Required
[if_not_found] Where a valid match is not found, return the [if_not_found] text you
supply.
Optional
If a valid match is not found, and [if_not_found] is missing, #N/A is
returned.
[match_mode] Specify the match type:
Optional 0 - Exact match. If none found, return #N/A. This is the default.
-1 - Exact match. If none found, return the next smaller item.
1 - Exact match. If none found, return the next larger item.
2 - A wildcard match where *, ?, and ~ have special meaning.
[search_mode] Specify the search mode to use:
Optional 1 - Perform a search starting at the first item. This is the default.
-1 - Perform a reverse search starting at the last item.
2 - Perform a binary search that relies on lookup_array being sorted
in ascending order. If not sorted, invalid results will be returned.
-2 - Perform a binary search that relies on lookup_array being sorted
in descending order. If not sorted, invalid results will be returned.
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
Examples
Example 1 uses XLOOKUP to look up a country name in a range, and then return its telephone
country code. It includes the lookup_value (cell F2), lookup_array (range B2:B11),
and return_array (range D2:D11) arguments. It doesn't include the match_mode argument, as
XLOOKUP produces an exact match by default.
Note: XLOOKUP uses a lookup array and a return array, whereas VLOOKUP uses a single table
array followed by a column index number. The equivalent VLOOKUP formula in this case would
be: =VLOOKUP(F2,B2:D11,3,FALSE)
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
Example 2 looks up employee information based on an employee ID number. Unlike VLOOKUP,
XLOOKUP can return an array with multiple items, so a single formula can return both employee
name and department from cells C5:D14.
Example 3 adds an if_not_found argument to the preceding example.
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
Example 4 looks in column C for the personal income entered in cell E2, and finds a matching
tax rate in column B. It sets the if_not_found argument to return 0 (zero) if nothing is found.
The match_mode argument is set to 1, which means the function will look for an exact match, and
if it can't find one, it returns the next larger item. Finally, the search_mode argument is set to 1,
which means the function will search from the first item to the last.
Note: XARRAY's lookup_array column is to the right of the return_array column, whereas
VLOOKUP can only look from left-to-right.
———————————————————————————
Example 5 uses a nested XLOOKUP function to perform both a vertical and horizontal match. It
first looks for Gross Profit in column B, then looks for Qtr1 in the top row of the table (range
C5:F5), and finally returns the value at the intersection of the two. This is similar to using
the INDEX and MATCH functions together.
Tip: You can also use XLOOKUP to replace the HLOOKUP function.
Note: The formula in cells D3:F3
is: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)).
———————————————————————————
IT Application Tools in Business – MS Excel Module Functions
Republic of the Philippines
Bataan Peninsula State University
Don Manuel Banzon Avenue, Balanga City, Bataan
Balanga Campus
Example 6 uses the SUM function, and two nested XLOOKUP functions, to sum all the values
between two ranges. In this case, we want to sum the values for grapes, bananas, and include
pears, which are between the two.
The formula in cell E3 is: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
How does it work? XLOOKUP returns a range, so when it calculates, the formula ends up looking
like this: =SUM($E$7:$E$9). You can see how this works on your own by selecting a cell with an
XLOOKUP formula similar to this one, then select Formulas > Formula Auditing > Evaluate
Formula, and then select Evaluate to step through the calculation.
References
[Link]
93a18ad188a1
[Link]
e1ad662ed95f
[Link]
88eae8bf5929
[Link]
IT Application Tools in Business – MS Excel Module Functions