Scribd
Upload
3 views2 pages

Advanced Excel Formulas

The document provides an overview of advanced Excel formulas including LARGE/SMALL for indexing values, SWITCH for conditional comparisons, FILTER and SORT for data management, 2-Way Lookup for fetching values based on row and column matches, Partial Lookup for text matching with wildcards, and XMATCH for enhanced position retrieval. Each formula is accompanied by definitions and examples for clarity. This serves as a guide for users looking to enhance their Excel skills with these functions.

Uploaded by

analytics.fusion.lab
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views2 pages

Advanced Excel Formulas

The document provides an overview of advanced Excel formulas including LARGE/SMALL for indexing values, SWITCH for conditional comparisons, FILTER and SORT for data management, 2-Way Lookup for fetching values based on row and column matches, Partial Lookup for text matching with wildcards, and XMATCH for enhanced position retrieval. Each formula is accompanied by definitions and examples for clarity. This serves as a guide for users looking to enhance their Excel skills with these functions.

Uploaded by

analytics.fusion.lab
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Advanced Excel Formulas

Prepared By: Vikas Kumar Singh

1. LARGE / SMALL (Indexing Values)


Definition: LARGE returns the nth highest value, and SMALL returns the nth lowest value.

Student Score
Asha 78
Rohan 90
Meera 65
John 88
Kunal 72

Examples:
Highest Score: =LARGE(B2:B6,1)
2nd Highest Score: =LARGE(B2:B6,2)
Lowest Score: =SMALL(B2:B6,1)

2. SWITCH Function
Definition: SWITCH compares a value with multiple conditions and returns a result.

Grade Meaning
A Excellent
B Good
C Average
D Poor

Formula Example:
=SWITCH(A2,"A","Excellent","B","Good","C","Average","D","Poor","Invalid Grade")

3. FILTER & SORT


FILTER returns only rows meeting a condition, and SORT arranges data in order.

Name Department Salary


Neha HR 45000
Ravi IT 60000
Sonal Finance 52000
Ajay IT 58000

Filter IT Employees: =FILTER(A2:C5,B2:B5="IT")


Sort Salary High to Low: =SORT(A2:C5,3,-1)

4. 2-Way Lookup
Definition: 2-Way Lookup fetches a value by matching both row and column.

Jan Feb Mar


Sales 45 55 65
Profit 12 14 18

Find Profit for Feb:


=INDEX(B2:D3, MATCH("Profit",A2:A3,0), MATCH("Feb",B1:D1,0))

5. Partial Lookup
Definition: Partial Lookup returns results based on partial text match using wildcards.

Product Code Product Name


TV32S TV 32 Inch
TV55L TV 55 Inch
FR520 Fridge 520L
WM7KG Washing Machine 7kg

Formula: =XLOOKUP("*"&F2;&"*",A2:A5,B2:B5)

6. XMATCH Function
Definition: XMATCH returns the position of a value with more features than MATCH.

City
Delhi
Mumbai
Chennai
Delhi

Examples:
Find Mumbai Position: =XMATCH("Mumbai",A2:A5)
Last Delhi Position: =XMATCH("Delhi",A2:A5,0,-1)
Partial Match “Che”: =XMATCH("Che*",A2:A5,2)

You might also like