LAB-2 INDEX, MATCH, UNIQUE, IFS, COUNTIFS, SUM

1 INDEX:
The INDEX function returns a value or the reference to a value from within a table or range.
Syntax: INDEX (array, row_num, [column_num])
The array form of the INDEX function has the following arguments:
 Array Required. A range of cells or an array constant.
 If array contains only one row or column, the corresponding row_num or column_num argument
is optional.
 If array has more than one row and more than one column, and only row_num or column_num is
used, INDEX returns an array of the entire row or column in array.
 row_num Required, unless column_num is present. Selects the row in array from which to
return a value. If row_num is omitted, column_num is required.
 column_num Optional. Selects the column in array from which to return a value. If
column_num is omitted, row_num is required.

1. Calculate Value at the intersection of the 2nd row and 2nd column in the range
& Value at the intersection of the 3rd row and 4th column in the range
&use Index and match function to retrieve the sales of the specific employee
based upon their name. & use Index and match function to retrieve the NAME of the specific region

DATA

NAME Region Orders Sales INDEX MATCH

MOHAN EAST 15 65000 WEST 3
RAJVEERWEST 45 63000 60000 9
KIRTHI SOUTH 67 60000 42000
NARESH NORTH 88 55000 RAJVEER =MATCH(178,D27:D36,0)
LAILA EAST 100 54000
SOUNDARNORTH 125 40000
RAJU EAST 140 42000 =INDEX(E27:E36,MATCH("RAJU",B27:B36,0))
MANI EAST 160 32000
SHIVU SOUTH 178 35000
SANTOSHEAST 190 31500

3 IFS Function:
 Use the IFS function to check whether one or more conditions are met and
returns a value that corresponds to the first TRUE condition.
 IFS can take the place of multiple nested IF statements, and is much easier
to read with multiple conditions.

Syntax:
= IFS(logical_test1, Value1 [logical_test2, Value2] …, [logical_test127, Value127])
3. Using Ifs Statement to Categorize the Sales Performance
=IFS (C3>90, "A", C3>80, "B", C3>70, "C",C3>60, "D")

NAME Region Orders Sales IFS ANSWER

 MOHAN EAST 25 65000 #N/A ### ==UNIQUE(C51:C60)'
RAJVEERWEST 12 63000 #N/A ###
KIRTHI SOUTH 67 60000 D ###
NARESH NORTH 88 55000 B ###
LAILA EAST 100 54000 A
SOUNDARNORTH 140 40000 A
RAJU EAST 140 40000 A
MANI EAST 160 32000 A
SHIVU SOUTH 178 35000 A
SANTOSHEAST 100 31500 A

=IFS(D53>90,"A",D53>80,"B",D53>70,"C",D53>60,"D")
5 COUNTIFS: 6 SUMIFS Function :
 The COUNTIFS function is a premade function in Excel, which counts cells in a range  The SUMIFS function is a
based on one or more true or false condition. range based on one or more t
Syntax =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)  It is typed =SUMIFS:
 The criteria_range1, criteria_range2, and so on, are the ranges where the function check Syntax=SUMIFS(sum_range
for the conditions.  The criteria_range1, criter
 If a number is greater than another number > for the conditions.
 If a number is smaller than another number <  If a number is greater than
 If a number is smaller than
 If a number or text is equa
 The [sum_range] is the ran
Use COUNTIFS, SUMIFS, AverageIFS, functions to calculate statistics based on
multiple Criteria, such as region, orders (>=50), sales.

COUNTIFS
NAME Region Orders Sales Region Orders PEOPLE
MOHAN EAST 15 65000 EAST >=50 2
RAJVEERWEST 45 63000 WEST >=50 1
KIRTHI SOUTH 67 60000 SOUTH >=50 2
NARESH NORTH 88 55000 NORTH >=50 2
LAILA EAST 100 54000
SOUNDARNORTH 140 40000
RAJU EAST 140 40000
MANI WEST 160 32000
SHIVU SOUTH 178 35000
SANTOSHEAST 15 31500
E, IFS, COUNTIFS, SUMIFS, AVERAGEIFS
2 MATCH function:
The MATCH function searches for a specified item in a range of cells, and then returns the relat
position of that item in the range.
Syntax: MATCH (lookup_value, lookup_array, [match_type])
 lookup_value Required. The value that you want to match in lookup_array.
n_num argument  lookup_array Required. The range of cells being searched.
 match_type Optional. The number -1, 0, or 1.
r column_num is

the specific region

q)MATCH FINDS THE LARGEST VALUE THAT IS LESS THAN OR EQUAL TO LOOK_VALUE
)MATCH FINDS THE EXACT EQUAL TO LOOK_VALUE

MATCH("RAJU",B27:B36,0))

4 UNIQUE:
The UNIQUE function returns a list of unique values in a list or range.
Syntax: =UNIQUE(array,[by_col],[exactly_once])
 Array (required) - the range or array from which to return unique values.
 By_col (optional) - a logical value indicating how to compare data:
o TRUE - compares data across columns.
o FALSE or omitted (default) - compares data across rows.
 Exactly_once (optional) - a logical value that defines what values are considered
unique:
o TRUE - returns values that occur only once, which is the database notion
of unique.
Value127]) o FALSE or omitted (default) - returns all distinct (different) values in the
range or array.

UNIQUE
ANSWER ANSWER
==UNIQUE(C51:C60)' ### ### '=UNIQUE(C51:D60,0)' ### ### =UNIQUE(D55:E60,1)
### ### ### ###
### ### ### ###
### ### ### ###
### ### ### ###
### ### ### ###
### ###
### ###
### ###

SUMIFS Function :
 The SUMIFS function is a premade function in Excel, which calculates the sum of a
range based on one or more true or false condition.
 It is typed =SUMIFS:
Syntax=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2] ...)
 The criteria_range1, criteria_range2, and so on, are the ranges where the function check
for the conditions.
 If a number is greater than another number >
 If a number is smaller than another number <
 If a number or text is equal to something =
 The [sum_range] is the range where the function calculates the sum.

SUMIFS =SUMIFS(D79:D88,B79:B88,J79,C79:C88,K79) AVERAGEIFS

Region Orders TOTAL SALES Region Orders AVERAGE SALES
EAST >=50 94000 EAST >=50 47000
WEST >=50 32000 WEST >=50 32000
SOUTH >=50 95000 SOUTH >=50 47500
NORTH >=50 95000 NORTH >=50 47500
and then returns the relative
=UNIQUE(D55:E60,1)
 LAB-3 VLOOKUP, HLOOKUP, XLOOKUP, COUNT, COUN
VLookup In Excel :
 This stands for the vertical lookup that is responsible for looking for a particular value in the
leftmost column of a table. It then returns a value in the same row from a column you specify.
 Syntax: VLOOKUP( lookup_value, table_array, col_index_num,[range_lookup]
 lookup_value - This is the value that you have to look for in the first column of a table.
 table - This indicates the table from which the value is retrieved.
 col_index - The column in the table from the value is to be retrieved. The Count Of first
Column is always 1.
 range_lookup - [optional] to find an Exact match enter False To find an appropriate
match enter True.
 TRUE = approximate match (default). FALSE = exact match.

EMP ID NAME LOCATION SALES

1001 JANANI CHENNAI 417,114.00
1002 SINDHU CHENNAI 258,752.00
1003 LAVANYA MUMBAI 425,599.00
1004 SINDHU PUNE 173,540.00
1005 GHAZALA DELHI 734,657.00
1006 SINDHU BANGALORE 185,809.00
1007 SHWETA HYDERABAD 411,175.00
1008 JANANI BANGALORE 335,541.00

XLOOKUP :
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.
Syntax=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode])
 lookup_value = The value to be searched
 lookup_array =The array or range of cells to search for values
 return_array= The array or range of Sales from which a corresponding Value to be
returned, based on the position of value in the look_up array.
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]
Optional
Specify the match type:
0 - Exact match. If none found, return #N/A. This is the default.
Err:509
1 - Exact match. If none found, return the next larger item.
2 - A wildcard match where *, ?, and ~ have special meaning.
 Use Xlookup to find the ORDER ID of an particular product based on their
product name for vertical table
XLOOKUP
Order ID Product Name Unit Price Qty Product Name Order ID
501 Mango 80 15 JAckFruit 502
502 JAckFruit 65 20 Apple 503
503 Apple 125 35 Mango 501
504 Grappes 45 48
505 Kiwi 150 20
Use Xlookup to find the Unit Price of a product based on their order id in
Horizontal Table.

Order ID 501 502 503 504 505

Product Name Mango JAckFruit Apple Grappes Kiwi
Unit Price 80 65 125 45 150
Qty 15 20 35 48 20

COUNT Explanation:
 The COUNT function is generally used to count a range of cells containing numbers or
dates excluding blanks.
Syntax: COUNT (value1, [value2], ...)
 The COUNT function syntax has the following arguments:
 Value1 Required. The first item, cell reference, or range within which you want to count
numbers.
 Value2, ... Optional. Up to 255 additional items, cell references, or ranges within which
you want to count numbers.

NAME MARKS COUNT COUNTALL

JANANI 40 10 =COUNT(B80:B92) 12 =COUNTA(B80:B92)
SINDHU 55
LAVANYA 47
SINDHU 69
GHAZALA
SINDHU 61
SHWETA AB
JANANI 98
SOUNDARYA 69
RAJU 74
MANI 70
SHIVU AB
SANTOSH 8/12/2025
 HLOOKUP in Excel stands for ‘Horizontal Lookup’. It is a function that makes Excel
search for a certain value in a row (the so called ‘table array’), in order to return a value
from a different row in the same column.
Syntax: HLOOKUP([value], [range], [row number], [false or true])
 The value you want to look up;
 The range in which you want to find the value and the return value;
 The number of the row within your defined range, that contains the return value;
 0 or FALSE for an exact match with the value your are looking for; 1 or TRUE for an
approximate match.

NAME MOHAN ABHISHEK RAJU SHOIB SOUNDARYA

JOINING DATE 2/1/2024 8/25/2023 6/14/2022 1/1/2025 10/10/2017
DEPT Human Resource Sales Legal Accounting Retail
LOOKUP, COUNT, COUNTA

VLOOKUP

RETRIEVE EMP NAME BASED ON EMP ID

EMP ID NAME
1001 JANANI =VLOOKUP(H17,B18:D25,2)
1008 JANANI
1005 GHAZALA
1002 SINDHU

ponding to
Order ID
=XLOOKUP(F49,B49:B53,A49:A53)

XLOOKUP
505 =XLOOKUP("Kiwi",B60:F60,B59:F59)

CountA Function :
 The COUNTA function counts the number of cells that are not empty in a ra
Syntax:
COUNTA(value1, [value2], ...)
The COUNTA function syntax has the following arguments:
 value1 Required. The first argument representing the values that you want to coun
 value2, ... Optional. Additional arguments representing the values that
you want to count, up to a maximum of 255 arguments.
 The COUNTA function counts cells containing any type of information,
including error values and empty text (""). For example, if the range
contains a formula that returns an empty string, the COUNTA function
counts that value.
The COUNTA function does not count empty cells.

=COUNTA(B80:B92)
 HLOOKUP

NAME DEPARTMENT
SOUNDARYA Retail

=HLOOKUP(J105,A104:F106,3,0)
at are not empty in a range.

ues that you want to count.

he values that

of information,

TA function