STATUS
Single Married
Married Separated
Separated
Ghosted
Widowed
One thing that messes with the mind is simply doing less than you can. - Jim Rohn
Vlook up Exercises
Formula Breakdown
Vlookup
Lookup Value Ano ang ipapahanap?
Table_array Saang table or database hahanapin o makikita ang ipinapahan
Column index no Pang ilang column ang gusto mong ipakita
True or False Approximate ba ibigsabihin may range or computation or exa
Employee Database
ID First Name
1 Miyan
ID First Name
1 Miyan
2 Michael
3 Anthony
4 Nikka
5 Mark
Something About Sweets
Sales Commission rate
225000 10.00%
Commission Scheme
50,000 2%
100,000 5%
200,000 10%
300,000 15%
500,000 20%
Product List
Sweets
Cup Cake
Brownies
Caramel Bar
Banofie Pie
Banana Bread
doing less than you can. - Jim Rohn
cises
hahanapin o makikita ang ipinapahanap mo?
usto mong ipakita
hin may range or computation or exacto na makikita sa database mo
e
Contact Num
123
Last Name Address Gender Contact Num
Reblora Cainta F 123
Zuniga Pasig M 456
Noble Pasig M 789
Alejar Makati F 1011
Bacsain Cebu M 1213
Total Commission
22500
e
2%
5%
10%
15%
20%
Is
Sweets
item in this table is also in the 1st table?
Cup Cake 1
#N/A
3
Banofie Pie 4
Banana Bread 5
Is item in this table is also in the 1st table?
Cup Cake TRUE
Cake FALSE
TRUE
TRUE
Banana Bread TRUE
JCV & Associates
Services
Engineering
HR
Operation
Accounting
JCV & Associates
Description
QS Unit
People Support
Fit Out
Finance
JCV & Associates
Services
Engineering
JCV & Associates
Description
#VALUE!
#VALUE!
#VALUE!
#VALUE!
Employee Number Employee Name Salary 13th Month
95-001 Michael ₱ 7,000.00 ₱ 4,000.00
95-002 Jhon ₱ 10,000.00 ₱ 5,000.00
95-003 Alex ₱ 13,000.00 ₱ 6,000.00
95-004 Mark ₱ 16,000.00 ₱ 7,000.00
95-005 Anthony ₱ 19,000.00 ₱ 8,000.00
95-006 Billy ₱ 22,000.00 ₱ 9,000.00
95-007 Nikki ₱ 25,000.00 ₱ 10,000.00
95-008 Lyka ₱ 28,000.00 ₱ 11,000.00
95-009 Miyan ₱ 31,000.00 ₱ 12,000.00
95-010 Paula ₱ 34,000.00 ₱ 13,000.00
Philhealth Contribution
0 ₱ 350.00
₱ 10,000.00 ₱ 500.00
₱ 20,000.00 ₱ 600.00
₱ 30,000.00 ₱ 700.00
SSS Contribution
0 ₱ 500.00
₱ 10,000.00 ₱ 700.00
₱ 20,000.00 ₱ 800.00
₱ 30,000.00 ₱ 1,000.00
Pag-ibig Contribution
0 ₱ 100.00
₱ 10,000.00 ₱ 300.00
₱ 20,000.00 ₱ 400.00
₱ 30,000.00 ₱ 500.00
Cellphone Number
09123456787 (977)-326-9991
Instruction: Create an automated payslip using Data Vaidation and Vlookup. Use
3/8/2025
Employee Number NET PAY
Employee Name
Salary 13th Month
PHILHEALTH
SSS
PAGIBIG
EMPLOYEE SHARE 50/50 share EMPLOYER SHARE
PHILHEALTH PHILHEALTH
SSS SSS
PAGIBIG PAGIBIG
aidation and Vlookup. Use the payslip reference table sheet as your source data.
50/50 share
2023 Employee Evaluation
ID # Employee Name Job Fit Culture Fit
001 Evangeline Santos 75 75
002 Nenita Trinidad 80 81
003 Leilani Alejandro 81 76
004 Yuri Dela Cruz 86 74
005 Enrico Reyes 85 73
006 Vicente Dimasalang 83 79
007 Michelle Marcelo 84 80
008 Manalo Ramos 85 81
009 Abel Dela Cruz 76 83
010 Mylene Endoma 78 82
Average = average of all gr
Remarks: Passed or Faile
Count If
Passed
Failed
23 Employee Evaluation
Boss Fit Other Criteria Average Remarks
75 76
90 73
76 78
75 72
0 74
85 71
69 75
71 76
82 72
90 80
Average = average of all grades
Remarks: Passed or Failed
Count If
Formula
Logical Test
Value if True
Value if False
Symbols
>
<
>=
<=
Formula Breakdown
If
Ano ang condition na gusto mo iset up?
Ano ipapakita o gagawin ni excel kapag tama o nameet ang condition na iyong isinet up?
Ano ipapakita o gagawin ni excel kapag mali o hindi nameet ang condition na iyong isinet up?
Symbols of Operators
Greater than
Less Than
Greater than or equal
Less Than or equal
CLIENT ACCOUNT RECEIVABLES
PHP 120,000.00
PHP 50,000.00
PHP 60,000.00
PHP 150,000.00
Remarks
Date Supplies In per kilo/pc Out per kilo/pc
20-Jun-24 5 7
20-Jun-24 10 8
20-Jun-24 7 11
21-Jun-24 8 12
21-Jun-24 14 2
21-Jun-24 20 15
22-Jun-24 3 1
23-Jun-24 10 4
24-Jun-24 5 5
Products Area
On hand per kilo/pc
-2
2
-4
-4
12
5
2
6
0
Total Sales
Date 3/8/2025
Supplies On hand per kilo/ pc
Products Area Total Sales
