Scribd
Upload
21 views17 pages

Advance Excel Course Practice File

The document provides an overview of various Excel formulas, including HLOOKUP, VLOOKUP, and IF functions, detailing their purposes and usage scenarios. It includes examples demonstrating how to use wildcards and multiple conditions within these formulas. Additionally, it encourages following on social media platforms for further learning resources.

Uploaded by

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

Advance Excel Course Practice File

The document provides an overview of various Excel formulas, including HLOOKUP, VLOOKUP, and IF functions, detailing their purposes and usage scenarios. It includes examples demonstrating how to use wildcards and multiple conditions within these formulas. Additionally, it encourages following on social media platforms for further learning resources.

Uploaded by

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

ADVANCE EXCEL FORMULA SERIES

Please Follow on Instagram !! https://www.instagram

Please Subscribe on YouTube !! https://www.youtube.c


EL FORMULA SERIES

https://www.instagram.com/excellearnersofficial/

https://www.youtube.com/@ExcelLearnersOfficial
"HLOOKUP" FORMULA
What Does this formula Do ?

• This function scans across the column headings at the top


of a table to find a specified item..

Product Item A
Sun 61
Days Item A Item B Item C Item D Item E Item F
Sun 61 104 87 150 114 126
Mon 56 63 122 100 54 137
Tue 60 116 121 89 132 128
Wed 63 133 70 122 95 115
Thu 101 63 81 98 75 69
Fri 70 53 101 91 135 146
Sat 146 110 56 50 85 93
"VLOOKUP" FORMULA
What Does this formula Do ?

• Looks for a value in the leftmost column in a


table, then returns a value in the same row from a
column you specify. By default, the table must be
sorted in an ascending order.

ID First name Last name


120 Luis Sousa
111 Michael Patten
105 Olivier Fontana
101 Sara Davis

ID First name Last name Title DOJ


101 Sara Davis Sales Rep. 1/1/2023
105 Olivier Fontana V.P. of Sales 1/5/2022
108 Karina Leal Sales Rep. 4/10/2020
111 Michael Patten Manager 10/19/2018
115 Brian Burke Sales Mgr. 5/15/2020
120 Luis Sousa Sales Rep. 9/13/2018
"VLOOKUP ARRAY" FORMULA
What Does this formula Do ?

• Looks for a value in the leftmost column in a


table, then returns a value in the same row from a
column you specify.

ID First name Last name Title DOJ


120 Luis Sousa Sales Rep. 9/13/2018
111 Michael Patten Manager 10/19/2018
105 Olivier Fontana V.P. of Sales 1/5/2022
111 Michael Patten Manager 10/19/2018

ID First name Last name Title DOJ


101 Sara Davis Sales Rep. 1/1/2023
105 Olivier Fontana V.P. of Sales 1/5/2022
108 Karina Leal Sales Rep. 4/10/2020
111 Michael Patten Manager 10/19/2018
115 Brian Burke Sales Mgr. 5/15/2020
120 Luis Sousa Sales Rep. 9/13/2018
"VLOOKUP WILDCARD (*)"
FORMULA Scenario-1
What Does this formula Do ?
• Asterstick (*) Helps to Remove the extra space.

Normal Vlookup With


VlookUp Wild Card

Sales Person 2021 2021


Aaron #N/A 168306
Adam #N/A 335115
Addison #N/A 156878
Aaliyah #N/A 196606
HISTORICAL SALES DATA
Sales Person 2020 2021 2022
Aaliyah Mai 318902 196606 170386
Aaron Garza 126867 168306 114482
Aaron Maldonado 314279 150664 100614
Abigail Garza 172622 318052 266112
Abigail Mejia 287727 161333 128946
Adam Nelson 305685 335115 216799
Thomas Do 161836 265864 333133
Addison Mehta 148735 156878 323458
"VLOOKUP WILDCARD (*)"
FORMULA Scenario-2
What Does this formula Do ?
• Asterstick (*) Helps to Remove the extra space.

Normal Vlookup With


VlookUp Wild Card

Sales Person 2021 2021


Aaron #N/A 168306
Adam #N/A 335115
Addison #N/A 156878
Aaliyah #N/A 196606
HISTORICAL SALES DATA
Sales Person 2020 2021 2022
Aaliyah 318902 196606 170386
Aaron 126867 168306 114482
Aaron 314279 150664 100614
Abigail 172622 318052 266112
Abigail 287727 161333 128946
Adam 305685 335115 216799
Thomas 161836 265864 333133
Addison 148735 156878 323458
"VLOOKUP WILDCARD (?)"
FORMULA Scenario-3
What Does this formula Do ?

• Question Mark (?) Helps to Remove the text.

Normal VlookUp Vlookup With


Wild Card
Invoice Sales Amt Sales Amt
Number
19835 #N/A ₹ 15,083.00
21895 #N/A ₹ 18,698.00
16879 #N/A ₹ 14,691.00
24895 #N/A ₹ 17,185.00

Invoice
Year Invoice No.
Amount
2022 INV22087 ₹ 18,210.00
2022 INV18804 ₹ 14,881.00
2022 INV19835 ₹ 15,083.00
2022 INV17353 ₹ 13,932.00
2022 INV22520 ₹ 12,789.00
2022 INV21895 ₹ 18,698.00
2022 INV16879 ₹ 14,691.00
2022 INV18359 ₹ 16,987.00
2022 INV17882 ₹ 14,766.00
2022 INV18334 ₹ 14,917.00
2022 INV24895 ₹ 17,185.00
2022 INV23153 ₹ 16,492.00
2022 INV21869 ₹ 12,474.00
"VLOOKUP WILDCARD (?)"
FORMULA Scenario-4
What Does this formula Do ?

• Question Mark (?) Helps to Remove the text.

Normal VlookUp Vlookup With


Wild Card
Invoice Sales Amt Sales Amt
Number
19835 #N/A ₹ 15,083.00
21895 #N/A ₹ 18,698.00
16879 #N/A ₹ 14,691.00
24895 #N/A ₹ 17,185.00

Invoice
Year Invoice No.
Amount
2022 22087-INV-2022 ₹ 18,210.00
2022 18804-INV-2022 ₹ 14,881.00
2022 19835-INV-2022 ₹ 15,083.00
2022 17353-INV-2022 ₹ 13,932.00
2022 22520-INV-2022 ₹ 12,789.00
2022 21895-INV-2022 ₹ 18,698.00
2022 16879-INV-2022 ₹ 14,691.00
2022 18359-INV-2022 ₹ 16,987.00
2022 17882-INV-2022 ₹ 14,766.00
2022 18334-INV-2022 ₹ 14,917.00
2022 24895-INV-2022 ₹ 17,185.00
2022 23153-INV-2022 ₹ 16,492.00
2022 21869-INV-2022 ₹ 12,474.00
"VLOOKUP WILDCARD (*~)"
FORMULA Scenario-5
What Does this formula Do ?
• Asterstick (*) & Tilde (*~) Helps to Remove the symbols.

Normal Vlookup With


VlookUp Wild Card

Sales Person 2021 2021


Aaliyah Mai #N/A 196606
Abigail Garza #N/A #N/A
Thomas Do #N/A 265864
Adam Nelson #N/A #N/A
HISTORICAL SALES DATA
Sales Person 2020 2021 2022
Aaliyah Mai** 318902 196606 170386
Aaron Garza? 126867 168306 114482
Aaron Maldonado 314279 150664 100614
Abigail Garza 172622 318052 266112
Abigail Mejia???? 287727 161333 128946
Adam Nelson*** 305685 335115 216799
Thomas Do~~ 161836 265864 333133
Addison Mehta 148735 156878 323458
"VLOOKUP FROM Multiple
Column" FORMULA
What Does this formula Do ?

• Looks for a value in the leftmost column in a


table, then returns a value in the same row from a
column you specify.

2 3 4 5
ID First name Last name Title DOJ
120 Luis Sousa Sales Rep. 9/13/2018
111 Michael Patten Manager 10/19/2018
105 Olivier Fontana V.P. of Sales 1/5/2022
111 Michael Patten Manager 10/19/2018

ID First name Last name Title DOJ


101 Sara Davis Sales Rep. 1/1/2023
105 Olivier Fontana V.P. of Sales 1/5/2022
108 Karina Leal Sales Rep. 4/10/2020
111 Michael Patten Manager 10/19/2018
115 Brian Burke Sales Mgr. 5/15/2020
120 Luis Sousa Sales Rep. 9/13/2018
"V LOOKUP USING COLUMNS"
FORMULA
What Does this formula Do ?

• Looks for a value in the leftmost column in a table, then


returns a value in the same row from a column you specify.
• Returns the numbers of column in an array or reference.

`
ID First name Last name Title DOJ
120 Luis Sousa Sales Rep. 9/13/2018
111 Michael Patten Manager 10/19/2018
105 Olivier Fontana V.P. of Sales 1/5/2022
115 Brian Burke Sales Mgr. 5/15/2020

ID First name Last name Title DOJ


101 Sara Davis Sales Rep. 1/1/2023
105 Olivier Fontana V.P. of Sales 1/5/2022
108 Karina Leal Sales Rep. 4/10/2020
111 Michael Patten Manager 10/19/2018
115 Brian Burke Sales Mgr. 5/15/2020
120 Luis Sousa Sales Rep. 9/13/2018
"IF""IF"
Formula in Excel
FORMULA
What Does this formula Do ?

• This function tests a condition.


• If the condition is met it is considered to be TRUE.
• If the condition is not met it is considered as FALSE.

=IF(Something is True, then do something,


otherwise do something else)
`
Voting Rights
A 18 Yes
` B 17 No

Target 10000 Good Job Keep It Up


`
Example 1 Example 2
Employee Overtime Target
Overtime Sales
Name Amt Met/Not
Sursh Yes 17000 500 Good Job
Mahesh Yes 13000 500 Good Job
Sukesh No 9800 0 Keep It Up
Rakesh Yes 11000 500 Good Job
Ajay No 9700 0 Keep It Up
Tarun No 13055 0 Good Job
"IF"IF"
(AND)" Formula
FORMULA
What Does this formula Do ?

• This function tests two or more conditions.


• If the condition is met it is considered to be TRUE.
• If the condition is not met it is considered as FALSE.
`
Target 10000 Good Job Keep It Up
`
`
Name QTR Jan Feb Mar
Result Sales Sales Sales
Sursh Keep It Up 7000 13678 21134
Mahesh Keep It Up 19000 9700 15355
Sukesh Good Job 16971 19343 15153
Rakesh Keep It Up 8600 17461 15380
Ajay Keep It Up 12000 9700 19309
Pradeep Good Job 16531 15400 16109
Tarun Good Job 20915 17677 21305
"IF (OR)"
"IF"Formula
FORMULAin Excel
What Does this formula Do ?

• This function tests two or more conditions.


• If the condition is met it is considered to be TRUE.
• If the condition is not met it is considered as FALSE.
`
Processing Charges
Cash Credit Card Wallet
0.00 50.00 50.00

Processing Customer Invoice Payment


Charges Name Amount Mode
- Sursh 5720 Cash
50.00 Mahesh 8792 Wallet
50.00 Sukesh 12098 Credit Card
- Rakesh 11000 Cash
50.00 Ajay 8955 Credit Card
50.00 Pradeep 7000 Wallet
50.00 Tarun 13055 Credit Card
"IF (NOT)"
"IF" Formula
FORMULAin Excel
What Does this formula Do ?

• This function performs a test to see if the test fails. (A type of


reverse logic).
• If the test fails, the result is TRUE.
• If the test is met, then the result is FALSE.
`
High Medium Low
15% Of 10% Of
Full Time 5% Of Salary
Condition Salary Salary

` Contract Flat 5% of Salary

Employee Job
Bonus Salary Status
Name Rating
Sursh 2700 18000 Full Time High
Mahesh 850 17000 Full Time Low
Sukesh 800 16000 Contract High
Rakesh 2300 23000 Full Time Medium
Ajay 1100 22000 Contract Medium
Pradeep 1150 23000 Contract Low
Tarun 1900 19000 Full Time Medium
cel

You might also like