Tips and Tricks in Advance Excel useful for Auditing

Day Sr. No. Tips Tricks

1 Basics of Vlookup
2 Data Validation and Vlookup
3 Excel Tables and Vlookup
4 WildCards and vlookup
5 Multiple Table Lookup
6 Nested Vlookup / Double Vlookup
7 Use of Match Function with VL
8 Reverse Lookup
9 Eliminating Zero From Vlookup
Day 1

10 Use of Iferror with Vlookup

11 Basics of Names Manager
12 For Quick Formula Creations
13 Navigations
14 Name with Countif Formula & use of wildcard
15 Creating Names quickly from Table Range
16 Define your own formulas using names
17 Use of relative & Absolute cell reference in names
18 Dynamic range creation using names
19 Store table data in names
20 IF Function Basisc
21 IF with AND
22 IF With OR
23 Nested IF
Day 2

24 Partial Text Lookup with IF

25 Basics of Macros
26 Recording and Execution of Macros
27 Basics of Excel Flash Fill
28 Uses of Flash Fill
By CA Bharath Gupta , Bellary Mobile No.
[email protected] 9886252374
Formula / Function Covered

Vlookup,
Wildcard Use,
Index,
Match,
Iferror, Xlookup

Names Manager,
Countif,
Data Validation,
Offset

IF, AND, OR, Isnumber, Search

Macros

Flash Fill
 Vlookup Basics
Vlookup (Exact Match)

Product Quantity Price Sales Product Sales

Cups 500 50 25000 Tennex
Box 400 40 16000
Guru 250 50 12500 Syntax Attributes
Captain 78 60 4680 Lookup_Value Value to be found
Tennex 84 55 4620 Table_array Table from where data to be retrieved
Col_index_num Column number from table array
[range_lookup] exact match or approximate match

Vlookup (Approximate Match)

Sales Commi. % Details Agent Sales Commi. % Commission

0 0.00% 0 - 9999 John 35000
10000 0.75% 10000 - 19999
20000 1.50% 20000 - 29999
30000 2.25% 30000 - 39999
40000 3.00% 40000 - 49999
50000 3.75% 50000 - 59999
60000 4.50% 60000 - 69999
70000 5.25% Above 69999
kup Basics

here data to be retrieved

ber from table array
or approximate match
 Data Validation &
VLOOKUP
Product Quantity Price Sales Product Sales
Cups 500 50 25000
Box 400 40 16000
Guru 250 50 12500
Captain 78 60 4680
Tennex 84 55 4620
 Excel Tables &
VLOOKUP
Product Quantity Price Sales Product Sales
Cups 500 50 25000
Box 400 40 16000
Guru 250 50 12500
Captain 78 60 4680 Product Sales
Tennex 84 55 4620
Tennex1 60 56 3360
Box1 70 51 3570
Guru3 60 33 1980
 Use of WildCard
with VLOOKUP
Client Name GSTIN Company GSTIN
ABC Trekkers Limited 27AACHT5766G1ZQ XYZ Softwares #N/A
XYZ Softwares Private Limited 36HHHRX5555Z1ZL Coca Cola
Coca Cola Private Limited 27AAACC4444R1ZO Axis Bank
Dominos Private Limted 09BBHHH8898H1WR ICICI Bank
Axis Bank Limited 10NNNHN7777H1ZE ABC Trekkers
ICICI Bank Limited 15IRUJF5574H1ZT Yogesh Food
Yogesh Food Products Limited 30UUURY3333U1ZM Dominos
 Names
Use of VLOOKUP for Indirect
Multiple Table Lookup Vlookup

XYZ Furniture Shop

Bill No. Quantity Type Size Price Total Sale Ply Commercial
1 142 Adhesive 2
2 106 PlyComm 6*4 Size
3 79 Adhesive 10 8*4
4 106 PlyComm 6*4 4*4
5 177 Adhesive 10 3*4
6 154 Adhesive 2 5*4
7 166 PlyComm 4*4 3*3
8 77 Adhesive 50 6*4
9 179 Adhesive 5
10 149 PlyWater 3*4 Ply Waterproof
11 181 PlyComm 3*4
12 38 PlyComm 6*4 Size
13 179 PlyWater 8*4 8*4
14 177 Adhesive 50 4*4
15 143 PlyComm 8*4 3*4
16 62 Adhesive 2 5*4
17 29 PlyComm 3*3 3*3
18 169 PlyWater 6*4 6*4
19 137 Adhesive 10
20 74 PlyWater 5*4
21 48 PlyWater 6*4
22 192 Adhesive 5
23 198 PlyWater 5*4
24 160 PlyComm 4*4
25 48 PlyComm 3*4
26 134 PlyComm 8*4
27 190 PlyWater 3*3
28 185 PlyWater 3*3
29 93 PlyWater 5*4
30 65 Adhesive 10
31 67 PlyWater 6*4
32 77 Adhesive 1
33 60 PlyWater 8*4
34 67 PlyComm 4*4
35 102 PlyWater 6*4
36 58 PlyComm 5*4
37 102 PlyWater 3*3
38 36 PlyComm 3*3
39 61 PlyWater 5*4
40 172 Adhesive 1
41 187 Adhesive 5
42 92 PlyComm 3*3
43 44 Adhesive 2
44 119 PlyWater 8*4
45 137 PlyWater 4*4
46 44 PlyComm 4*4
47 66 Adhesive 10
48 46 Adhesive 1
49 132 Adhesive 2
50 151 Adhesive 2
51 166 PlyComm 4*4
52 194 PlyWater 3*4
53 172 PlyWater 4*4
54 77 PlyWater 6*4
55 71 PlyWater 3*4
56 130 PlyWater 4*4
57 123 Adhesive 5
58 35 Adhesive 50
59 124 Adhesive 5
60 132 PlyWater 3*3
61 105 PlyComm 3*3
62 157 PlyComm 4*4
63 52 PlyWater 4*4
64 178 PlyWater 4*4
65 124 Adhesive 1
66 67 PlyComm 3*4
67 87 PlyComm 3*3
68 68 PlyWater 8*4
69 31 PlyComm 5*4
70 42 PlyWater 6*4
71 119 Adhesive 1
72 145 Adhesive 10
73 98 PlyWater 3*3
74 90 Adhesive 1
75 195 PlyComm 8*4
76 137 Adhesive 1
77 59 PlyComm 6*4
78 62 PlyWater 4*4
79 126 PlyWater 3*3
80 97 PlyWater 4*4
81 90 PlyComm 4*4
82 140 PlyWater 6*4
83 100 PlyWater 6*4
84 129 PlyWater 4*4
85 87 Adhesive 2
86 108 PlyComm 6*4
87 135 PlyWater 3*4
88 161 Adhesive 10
89 164 Adhesive 2
 90 189 Adhesive 10
91 41 PlyComm 5*4
92 100 PlyWater 3*3
93 144 PlyComm 6*4
94 174 PlyComm 6*4
95 75 Adhesive 1
96 184 PlyComm 6*4
97 45 Adhesive 1
98 157 PlyWater 4*4
99 200 Adhesive 5
100 108 PlyComm 6*4
Ply Commercial Adhesives

Rate Per Sq Ft Total Cost per unit Weight (KG) Cost per Kg Total Cost per unit
32 1024 1 60 60
35 560 2 58 116
35 420 5 55 275
33 660 10 50 500
38 342 50 46 2300
33 792

Ply Waterproof

Rate Per Sq Ft Total Cost per unit

54 1728
57 912
57 684
55 1100
60 540
55 1320
 Nested VLOOKUP

Customer Name Address City Bill No. Customer Name Amount GST
Arjun Bhanushali Flat No. 3 Panaji 1500 Gajodhar Lahoti 10700 1926
Arjun Lahoti Flat No. 15 Jaipur 1501 Gayatri Pande 5900 1062
Arjun Mushtak Flat No. 6 Delhi 1502 Arjun Shashtri 11600 2088
Arjun Pande Flat No. 11 Ajmer 1503 Shahrukh Randad 8800 1584
Arjun Randad Flat No. 1 Anand 1504 John Mushtak 10400 1872
Arjun Shashtri Flat No. 12 Jaipur 1505 Vivek Shukla 11700 2106
Arjun Shukla Flat No. 7 Ajmer 1506 John Shukla 6800 1224
Arjun Tiwari Flat No. 7 Panaji 1507 Gayatri Bhanushali 12900 2322
Gajodhar Bhanushali Flat No. 5 Kolkata 1508 Shahrukh Pande 6200 1116
Gajodhar Khan Flat No. 7 Aurangabad 1509 Vivek Shukla 6400 1152
Gajodhar Lahoti Flat No. 15 Jaipur 1510 Shahrukh Randad 13800 2484
Gajodhar Mushtak Flat No. 3 Kolkata 1511 Vivek Shukla 13600 2448
Gajodhar Pande Flat No. 8 Nagpur 1512 John Shashtri 12100 2178
Gajodhar Randad Flat No. 14 Ajmer 1513 Salman Shashtri 11800 2124
Gajodhar Shashtri Flat No. 12 Anand 1514 Vivek Mushtak 13900 2502
Gajodhar Shukla Flat No. 12 Aurangabad 1515 Vivek Mushtak 6500 1170
Gajodhar Tiwari Flat No. 14 Aurangabad 1516 Gayatri Pande 14600 2628
Gayatri Bhanushali Flat No. 1 Aurangabad 1517 Arjun Shukla 11500 2070
Gayatri Khan Flat No. 8 Anand 1518 John Khan 5900 1062
Gayatri Lahoti Flat No. 7 Panaji 1519 Shahrukh Shashtri 9300 1674
Gayatri Mushtak Flat No. 7 Aurangabad 1520 Pravin Lahoti 5400 972
Gayatri Pande Flat No. 8 Jaipur 1521 John Lahoti 10700 1926
Gayatri Shashtri Flat No. 7 Jaipur 1522 Pravin Khan 14300 2574
Gayatri Shukla Flat No. 15 Nagpur 1523 Shahrukh Shashtri 9400 1692
Gayatri Tiwari Flat No. 6 Aurangabad 1524 Arjun Mushtak 5500 990
John Bhanushali Flat No. 1 Nagpur 1525 Gayatri Lahoti 7500 1350
John Khan Flat No. 1 Solapur 1526 Salman Khan 9600 1728
John Lahoti Flat No. 10 Nagpur 1527 Sam Lahoti 14400 2592
John Mushtak Flat No. 11 Ajmer 1528 Gayatri Pande 7100 1278
John Pande Flat No. 9 Ajmer 1529 Salman Pande 11300 2034
John Randad Flat No. 3 Ajmer 1530 Pravin Randad 8700 1566
John Shashtri Flat No. 3 Ajmer 1531 Shahrukh Shukla 5200 936
John Shukla Flat No. 15 Aurangabad 1532 Gayatri Lahoti 10500 1890
John Tiwari Flat No. 9 Ajmer 1533 Sam Bhanushali 9800 1764
Pravin Bhanushali Flat No. 8 Nagpur 1534 Arjun Shashtri 5100 918
Pravin Khan Flat No. 14 Jaipur 1535 Shahrukh Shashtri 13900 2502
Pravin Lahoti Flat No. 5 Jaipur 1536 Shahrukh Randad 11800 2124
Pravin Mushtak Flat No. 3 Kolkata 1537 Vivek Randad 11400 2052
Pravin Randad Flat No. 1 Kolkata 1538 Gajodhar Pande 8700 1566
 Pravin Shashtri Flat No. 2 Aurangabad 1539 Shahrukh Lahoti 5000 900
Pravin Shukla Flat No. 14 Delhi 1540 Gayatri Bhanushali 11600 2088
Pravin Tiwari Flat No. 3 Ajmer 1541 John Shashtri 6700 1206
Salman Bhanushali Flat No. 12 Jaipur 1542 Sam Randad 13900 2502
Salman Khan Flat No. 10 Panaji 1543 John Khan 6800 1224
Salman Lahoti Flat No. 6 Panaji 1544 Gajodhar Shashtri 10500 1890
Salman Mushtak Flat No. 5 Solapur 1545 Pravin Lahoti 6800 1224
Salman Pande Flat No. 6 Solapur 1546 Vivek Shukla 8300 1494
Salman Randad Flat No. 5 Nagpur 1547 Gayatri Lahoti 7300 1314
Salman Shashtri Flat No. 10 Nagpur 1548 Sam Shukla 8600 1548
Salman Shukla Flat No. 1 Nagpur 1549 Sam Khan 10200 1836
Salman Tiwari Flat No. 14 Delhi 1550 John Shashtri 14500 2610
Sam Bhanushali Flat No. 5 Kolkata 1551 Sam Shashtri 13800 2484
Sam Khan Flat No. 7 Nagpur 1552 Pravin Lahoti 13700 2466
Sam Lahoti Flat No. 2 Anand 1553 John Shashtri 14300 2574
Sam Mushtak Flat No. 8 Panaji 1554 Vivek Pande 9600 1728
Sam Pande Flat No. 10 Jaipur 1555 Sam Pande 9600 1728
Sam Randad Flat No. 13 Solapur 1556 John Khan 6500 1170
Sam Shashtri Flat No. 8 Aurangabad 1557 John Lahoti 14800 2664
Sam Shukla Flat No. 4 Aurangabad 1558 Pravin Lahoti 14300 2574
Shahrukh Bhanushali Flat No. 6 Anand 1559 Sam Mushtak 14700 2646
Shahrukh Khan Flat No. 1 Solapur 1560 Gajodhar Shashtri 12300 2214
Shahrukh Lahoti Flat No. 1 Anand 1561 Gajodhar Lahoti 14500 2610
Shahrukh Mushtak Flat No. 15 Panaji 1562 Gayatri Shukla 10800 1944
Shahrukh Pande Flat No. 14 Ajmer 1563 Gajodhar Khan 10400 1872
Shahrukh Randad Flat No. 12 Aurangabad 1564 Gayatri Lahoti 9800 1764
Shahrukh Shashtri Flat No. 7 Anand 1565 John Khan 8100 1458
Shahrukh Shukla Flat No. 5 Anand 1566 Arjun Shukla 13700 2466
Shahrukh Tiwari Flat No. 15 Aurangabad 1567 Gajodhar Khan 8600 1548
Vivek Bhanushali Flat No. 10 Kolkata 1568 Salman Lahoti 6000 1080
Vivek Khan Flat No. 9 Aurangabad 1569 John Lahoti 12900 2322
Vivek Lahoti Flat No. 14 Solapur 1570 Gayatri Tiwari 12300 2214
Vivek Mushtak Flat No. 13 Kolkata 1571 Shahrukh Bhanushali 5400 972
Vivek Pande Flat No. 5 Panaji 1572 Arjun Bhanushali 13600 2448
Vivek Randad Flat No. 1 Ajmer 1573 Arjun Randad 13500 2430
Vivek Shashtri Flat No. 8 Ajmer 1574 Sam Khan 9400 1692
Vivek Shukla Flat No. 13 Jaipur 1575 Sam Shashtri 6500 1170
Vivek Tiwari Flat No. 6 Jaipur 1576 Arjun Bhanushali 9100 1638
1577 Sam Pande 13700 2466
1578 Salman Lahoti 10300 1854
1579 Shahrukh Shukla 10000 1800
1580 Salman Bhanushali 5300 954
1581 Arjun Lahoti 13600 2448
1582 Pravin Shukla 13100 2358
1583 John Randad 6800 1224
1584 Vivek Lahoti 14900 2682
1585 Gajodhar Shashtri 13600 2448
1586 Vivek Pande 6000 1080
1587 Gayatri Khan 9500 1710
1588 Sam Shukla 12000 2160
1589 Shahrukh Khan 12300 2214
1590 Vivek Pande 12300 2214
1591 Shahrukh Shashtri 14600 2628
1592 Gajodhar Lahoti 11100 1998
1593 Vivek Tiwari 5400 972
1594 Arjun Randad 8900 1602
1595 Shahrukh Tiwari 12400 2232
1596 Pravin Mushtak 9000 1620
1597 Gajodhar Shukla 13200 2376
1598 John Bhanushali 10000 1800
1599 Vivek Pande 9900 1782
1600 Gajodhar Shukla 9600 1728
1601 Salman Khan 6400 1152
1602 Gayatri Tiwari 14000 2520
1603 John Randad 11000 1980
1604 Pravin Lahoti 14500 2610
1605 Pravin Randad 9200 1656
1606 Salman Randad 14400 2592
1607 Arjun Mushtak 11600 2088
1608 Gayatri Tiwari 8700 1566
1609 Pravin Shukla 9900 1782
1610 Gajodhar Khan 10700 1926
1611 Sam Mushtak 6500 1170
1612 Gajodhar Khan 5200 936
1613 Gajodhar Tiwari 7400 1332
1614 Pravin Bhanushali 11200 2016
1615 Vivek Tiwari 10100 1818
1616 Gajodhar Bhanushali 5100 918
1617 Vivek Randad 11300 2034
1618 Sam Lahoti 5100 918
1619 Gayatri Khan 8700 1566
1620 Vivek Khan 14900 2682
1621 Arjun Randad 10000 1800
1622 Salman Bhanushali 9500 1710
1623 Shahrukh Mushtak 11500 2070
1624 Salman Pande 7200 1296
1625 Vivek Randad 5600 1008
1626 Shahrukh Randad 13200 2376
1627 Shahrukh Khan 7900 1422
1628 John Pande 11300 2034
1629 Salman Mushtak 5000 900
1630 Pravin Bhanushali 13300 2394
1631 Gajodhar Shashtri 10300 1854
1632 Vivek Shashtri 7800 1404
1633 Vivek Shukla 9100 1638
1634 Sam Khan 10300 1854
1635 Gayatri Mushtak 8000 1440
1636 Shahrukh Lahoti 9200 1656
1637 John Shukla 10400 1872
1638 Shahrukh Pande 8500 1530
1639 Pravin Randad 11900 2142
1640 Pravin Shukla 7300 1314
1641 Pravin Bhanushali 11300 2034
1642 Pravin Mushtak 8400 1512
1643 Gayatri Mushtak 9200 1656
1644 Salman Shukla 6400 1152
1645 Gayatri Bhanushali 7000 1260
1646 Gajodhar Mushtak 13800 2484
1647 Vivek Shukla 11900 2142
1648 Gajodhar Shukla 5200 936
1649 Gayatri Lahoti 14800 2664
1650 Gayatri Khan 6700 1206
1651 Vivek Mushtak 7300 1314
1652 Pravin Shashtri 8900 1602
1653 Vivek Bhanushali 9800 1764
1654 Arjun Shukla 8800 1584
1655 Shahrukh Bhanushali 8400 1512
1656 John Tiwari 7900 1422
1657 Pravin Shukla 7600 1368
1658 Sam Bhanushali 13500 2430
1659 Salman Shashtri 12700 2286
1660 John Khan 11100 1998
1661 Shahrukh Tiwari 9500 1710
1662 Gayatri Shashtri 7100 1278
1663 Arjun Randad 5400 972
1664 Shahrukh Khan 7500 1350
1665 Arjun Shashtri 14900 2682
1666 Arjun Randad 13200 2376
1667 Shahrukh Khan 8200 1476
1668 Salman Mushtak 9000 1620
1669 Gayatri Khan 9800 1764
1670 Pravin Tiwari 7300 1314
1671 Vivek Lahoti 10400 1872
1672 Vivek Bhanushali 12600 2268
1673 Shahrukh Khan 9900 1782
1674 Shahrukh Lahoti 9000 1620
1675 Vivek Pande 14600 2628
1676 John Randad 9500 1710
1677 Salman Tiwari 9700 1746
1678 Arjun Mushtak 11600 2088
1679 Shahrukh Lahoti 6000 1080
1680 Arjun Tiwari 9500 1710
1681 John Mushtak 6800 1224
1682 John Lahoti 14600 2628
1683 Sam Lahoti 12700 2286
1684 Gayatri Mushtak 10400 1872
1685 Vivek Mushtak 12700 2286
1686 Vivek Randad 9000 1620
1687 John Shukla 6300 1134
1688 Arjun Shashtri 6000 1080
1689 Shahrukh Lahoti 5700 1026
1690 Vivek Shashtri 13500 2430
1691 Gayatri Khan 11500 2070
1692 Sam Mushtak 11100 1998
1693 John Bhanushali 9500 1710
1694 Shahrukh Khan 5800 1044
1695 Sam Khan 13200 2376
1696 Gayatri Lahoti 8500 1530
1697 Gajodhar Randad 6600 1188
1698 Salman Shukla 6300 1134
1699 Vivek Tiwari 10600 1908
1700 Arjun Pande 7400 1332
Total
12626 Bill No. Customer Name
6962
13688
10384
12272 Customer Name City
13806
8024
15222
7316 Bill No. City
7552
16284
16048
14278
13924
16402
7670
17228
13570
6962
10974
6372
12626
16874
11092
6490
8850
11328
16992
8378
13334
10266
6136
12390
11564
6018
16402
13924
13452
10266
 5900
13688
7906
16402
8024
12390
8024
9794
8614
10148
12036
17110
16284
16166
16874
11328
11328
7670
17464
16874
17346
14514
17110
12744
12272
11564
9558
16166
10148
7080
15222
14514
6372
16048
15930
11092
7670
10738
16166
12154
11800
6254
16048
15458
8024
17582
16048
7080
11210
14160
14514
14514
17228
13098
6372
10502
14632
10620
15576
11800
11682
11328
7552
16520
12980
17110
10856
16992
13688
10266
11682
12626
7670
6136
8732
13216
11918
6018
13334
6018
10266
17582
11800
11210
13570
8496
6608
15576
9322
13334
 5900
15694
12154
9204
10738
12154
9440
10856
12272
10030
14042
8614
13334
9912
10856
7552
8260
16284
14042
6136
17464
7906
8614
10502
11564
10384
9912
9322
8968
15930
14986
13098
11210
8378
6372
8850
17582
15576
9676
10620
11564
8614
12272
14868
11682
10620
17228
11210
11446
13688
7080
11210
8024
17228
14986
12272
14986
10620
7434
7080
6726
15930
13570
13098
11210
6844
15576
10030
7788
7434
12508
8732
 Use of MATCH with VLOOKUP
Employee City State Zip E-mail
Himesh Kolkatta WB 981147 [email protected]
Utsav Mumbai MH 981317 [email protected]
Shila Aurangabad MH 981335 [email protected]
Samantha New Delhi ND 981118 [email protected]
George Deharadoon UK 981244 [email protected]
Narendra Nagpur MH 981162 [email protected]
Kedar Rajkot GJ 981177 [email protected]
Samir Chennai TN 981143 [email protected]
Pankaj Hydrabad TS 981280 [email protected]
Ravi Jaipur RJ 981266 [email protected]

Employee Utsav Utsav

Zip
State
Address
E-mail
Phone
Phone Joining Date Salary
7588926495 11/11/2005 Rs. 80,000.00
7588448077 10/8/2008 Rs. 35,000.00
7588219074 3/7/2013 Rs. 40,000.00
7588100086 5/17/2012 Rs. 35,751.00
7588878930 3/28/2011 Rs. 61,883.00
7588968283 4/10/2004 Rs. 70,000.00
7588008819 6/16/2005 Rs. 57,280.00
7588915699 11/15/2008 Rs. 63,015.00
7588140885 8/14/2008 Rs. 54,005.00
7588761044 1/9/2009 Rs. 41,404.00
 Customer Name Address City Bill No. Amount GST Total
Gajodhar Lahoti Flat No. 15 Jaipur 1500 10700 1926 12626
Gayatri Pande Flat No. 8 Jaipur 1501 5900 1062 6962
Arjun Shashtri Flat No. 12 Jaipur 1502 11600 2088 13688
Shahrukh Randad Flat No. 12 Aurangabad 1503 8800 1584 10384
John Mushtak Flat No. 11 Ajmer 1504 10400 1872 12272
Vivek Shukla Flat No. 13 Jaipur 1505 11700 2106 13806
John Shukla Flat No. 15 Aurangabad 1506 6800 1224 8024
Gayatri Bhanushali Flat No. 1 Aurangabad 1507 12900 2322 15222
Shahrukh Pande Flat No. 14 Ajmer 1508 6200 1116 7316
Vivek Shukla Flat No. 12 Solapur 1509 6400 1152 7552
Shahrukh Randad Flat No. 10 Delhi 1510 13800 2484 16284
Vivek Shukla Flat No. 5 Anand 1511 13600 2448 16048
John Shashtri Flat No. 3 Ajmer 1512 12100 2178 14278
Salman Shashtri Flat No. 10 Nagpur 1513 11800 2124 13924
Vivek Mushtak Flat No. 13 Kolkata 1514 13900 2502 16402
Vivek Mushtak Flat No. 1 Jaipur 1515 6500 1170 7670
Gayatri Pande Flat No. 6 Ajmer 1516 14600 2628 17228
Arjun Shukla Flat No. 7 Ajmer 1517 11500 2070 13570
John Khan Flat No. 1 Solapur 1518 5900 1062 6962
Shahrukh Shashtri Flat No. 7 Anand 1519 9300 1674 10974
Pravin Lahoti Flat No. 5 Jaipur 1520 5400 972 6372
John Lahoti Flat No. 10 Nagpur 1521 10700 1926 12626
Pravin Khan Flat No. 14 Jaipur 1522 14300 2574 16874
Shahrukh Shashtri Flat No. 15 Anand 1523 9400 1692 11092
Arjun Mushtak Flat No. 6 Delhi 1524 5500 990 6490
Gayatri Lahoti Flat No. 7 Panaji 1525 7500 1350 8850
Salman Khan Flat No. 10 Panaji 1526 9600 1728 11328
Sam Lahoti Flat No. 2 Anand 1527 14400 2592 16992
Gayatri Pande Flat No. 9 Kolkata 1528 7100 1278 8378
Salman Pande Flat No. 6 Solapur 1529 11300 2034 13334
Pravin Randad Flat No. 1 Kolkata 1530 8700 1566 10266
Shahrukh Shukla Flat No. 5 Anand 1531 5200 936 6136
Gayatri Lahoti Flat No. 2 Delhi 1532 10500 1890 12390
Sam Bhanushali Flat No. 5 Kolkata 1533 9800 1764 11564
Arjun Shashtri Flat No. 2 Nagpur 1534 5100 918 6018
Shahrukh Shashtri Flat No. 6 Solapur 1535 13900 2502 16402
Shahrukh Randad Flat No. 8 Solapur 1536 11800 2124 13924
Vivek Randad Flat No. 1 Ajmer 1537 11400 2052 13452
Gajodhar Pande Flat No. 8 Nagpur 1538 8700 1566 10266
Shahrukh Lahoti Flat No. 1 Anand 1539 5000 900 5900
Gayatri Bhanushali Flat No. 9 Aurangabad 1540 11600 2088 13688
John Shashtri Flat No. 11 Delhi 1541 6700 1206 7906
Sam Randad Flat No. 13 Solapur 1542 13900 2502 16402
 John Khan Flat No. 15 Kolkata 1543 6800 1224 8024
Gajodhar Shashtri Flat No. 12 Anand 1544 10500 1890 12390
Pravin Lahoti Flat No. 11 Ajmer 1545 6800 1224 8024
Vivek Shukla Flat No. 5 Panaji 1546 8300 1494 9794
Gayatri Lahoti Flat No. 1 Aurangabad 1547 7300 1314 8614
Sam Shukla Flat No. 4 Aurangabad 1548 8600 1548 10148
Sam Khan Flat No. 7 Nagpur 1549 10200 1836 12036
John Shashtri Flat No. 11 Solapur 1550 14500 2610 17110
Sam Shashtri Flat No. 8 Aurangabad 1551 13800 2484 16284
Pravin Lahoti Flat No. 2 Panaji 1552 13700 2466 16166
John Shashtri Flat No. 9 Ajmer 1553 14300 2574 16874
Vivek Pande Flat No. 5 Panaji 1554 9600 1728 11328
Sam Pande Flat No. 10 Jaipur 1555 9600 1728 11328
John Khan Flat No. 9 Delhi 1556 6500 1170 7670
John Lahoti Flat No. 8 Kolkata 1557 14800 2664 17464
Pravin Lahoti Flat No. 7 Solapur 1558 14300 2574 16874
Sam Mushtak Flat No. 8 Panaji 1559 14700 2646 17346
Gajodhar Shashtri Flat No. 9 Kolkata 1560 12300 2214 14514
Gajodhar Lahoti Flat No. 12 Kolkata 1561 14500 2610 17110
Gayatri Shukla Flat No. 15 Nagpur 1562 10800 1944 12744
Gajodhar Khan Flat No. 7 Aurangabad 1563 10400 1872 12272
Gayatri Lahoti Flat No. 7 Kolkata 1564 9800 1764 11564
John Khan Flat No. 4 Ajmer 1565 8100 1458 9558
Arjun Shukla Flat No. 12 Delhi 1566 13700 2466 16166
Gajodhar Khan Flat No. 5 Nagpur 1567 8600 1548 10148
Salman Lahoti Flat No. 6 Panaji 1568 6000 1080 7080
John Lahoti Flat No. 7 Delhi 1569 12900 2322 15222
Gayatri Tiwari Flat No. 6 Aurangabad 1570 12300 2214 14514
Shahrukh Bhanushali Flat No. 6 Anand 1571 5400 972 6372
Arjun Bhanushali Flat No. 3 Panaji 1572 13600 2448 16048
Arjun Randad Flat No. 1 Anand 1573 13500 2430 15930
Sam Khan Flat No. 2 Nagpur 1574 9400 1692 11092
Sam Shashtri Flat No. 7 Jaipur 1575 6500 1170 7670
Arjun Bhanushali Flat No. 11 Panaji 1576 9100 1638 10738
Sam Pande Flat No. 9 Aurangabad 1577 13700 2466 16166
Salman Lahoti Flat No. 12 Kolkata 1578 10300 1854 12154
Shahrukh Shukla Flat No. 6 Aurangabad 1579 10000 1800 11800
Salman Bhanushali Flat No. 12 Jaipur 1580 5300 954 6254
Arjun Lahoti Flat No. 15 Jaipur 1581 13600 2448 16048
Pravin Shukla Flat No. 14 Delhi 1582 13100 2358 15458
John Randad Flat No. 3 Ajmer 1583 6800 1224 8024
Vivek Lahoti Flat No. 14 Solapur 1584 14900 2682 17582
Gajodhar Shashtri Flat No. 13 Aurangabad 1585 13600 2448 16048
Vivek Pande Flat No. 1 Delhi 1586 6000 1080 7080
Gayatri Khan Flat No. 8 Anand 1587 9500 1710 11210
 Sam Shukla Flat No. 6 Ajmer 1588 12000 2160 14160
Shahrukh Khan Flat No. 1 Solapur 1589 12300 2214 14514
Vivek Pande Flat No. 14 Nagpur 1590 12300 2214 14514
Shahrukh Shashtri Flat No. 12 Solapur 1591 14600 2628 17228
Gajodhar Lahoti Flat No. 15 Solapur 1592 11100 1998 13098
Vivek Tiwari Flat No. 6 Jaipur 1593 5400 972 6372
Arjun Randad Flat No. 10 Anand 1594 8900 1602 10502
Shahrukh Tiwari Flat No. 15 Aurangabad 1595 12400 2232 14632
Pravin Mushtak Flat No. 3 Kolkata 1596 9000 1620 10620
Gajodhar Shukla Flat No. 12 Aurangabad 1597 13200 2376 15576
John Bhanushali Flat No. 1 Nagpur 1598 10000 1800 11800
Vivek Pande Flat No. 15 Solapur 1599 9900 1782 11682
Gajodhar Shukla Flat No. 12 Delhi 1600 9600 1728 11328
Salman Khan Flat No. 9 Ajmer 1601 6400 1152 7552
Gayatri Tiwari Flat No. 10 Ajmer 1602 14000 2520 16520
John Randad Flat No. 5 Kolkata 1603 11000 1980 12980
Pravin Lahoti Flat No. 10 Anand 1604 14500 2610 17110
Pravin Randad Flat No. 4 Panaji 1605 9200 1656 10856
Salman Randad Flat No. 5 Nagpur 1606 14400 2592 16992
Arjun Mushtak Flat No. 2 Anand 1607 11600 2088 13688
Gayatri Tiwari Flat No. 5 Anand 1608 8700 1566 10266
Pravin Shukla Flat No. 1 Ajmer 1609 9900 1782 11682
Gajodhar Khan Flat No. 15 Anand 1610 10700 1926 12626
Sam Mushtak Flat No. 14 Panaji 1611 6500 1170 7670
Gajodhar Khan Flat No. 3 Jaipur 1612 5200 936 6136
Gajodhar Tiwari Flat No. 14 Aurangabad 1613 7400 1332 8732
Pravin Bhanushali Flat No. 8 Nagpur 1614 11200 2016 13216
Vivek Tiwari Flat No. 11 Aurangabad 1615 10100 1818 11918
Gajodhar Bhanushali Flat No. 5 Kolkata 1616 5100 918 6018
Vivek Randad Flat No. 15 Delhi 1617 11300 2034 13334
Sam Lahoti Flat No. 15 Panaji 1618 5100 918 6018
Gayatri Khan Flat No. 12 Nagpur 1619 8700 1566 10266
Vivek Khan Flat No. 9 Aurangabad 1620 14900 2682 17582
Arjun Randad Flat No. 11 Kolkata 1621 10000 1800 11800
Salman Bhanushali Flat No. 5 Anand 1622 9500 1710 11210
Shahrukh Mushtak Flat No. 15 Panaji 1623 11500 2070 13570
Salman Pande Flat No. 6 Panaji 1624 7200 1296 8496
Vivek Randad Flat No. 5 Ajmer 1625 5600 1008 6608
Shahrukh Randad Flat No. 7 Solapur 1626 13200 2376 15576
Shahrukh Khan Flat No. 14 Solapur 1627 7900 1422 9322
John Pande Flat No. 9 Ajmer 1628 11300 2034 13334
Salman Mushtak Flat No. 5 Solapur 1629 5000 900 5900
Pravin Bhanushali Flat No. 12 Panaji 1630 13300 2394 15694
Gajodhar Shashtri Flat No. 9 Delhi 1631 10300 1854 12154
Vivek Shashtri Flat No. 8 Ajmer 1632 7800 1404 9204
 Vivek Shukla Flat No. 7 Anand 1633 9100 1638 10738
Sam Khan Flat No. 1 Panaji 1634 10300 1854 12154
Gayatri Mushtak Flat No. 7 Aurangabad 1635 8000 1440 9440
Shahrukh Lahoti Flat No. 13 Jaipur 1636 9200 1656 10856
John Shukla Flat No. 13 Jaipur 1637 10400 1872 12272
Shahrukh Pande Flat No. 4 Delhi 1638 8500 1530 10030
Pravin Randad Flat No. 13 Panaji 1639 11900 2142 14042
Pravin Shukla Flat No. 12 Panaji 1640 7300 1314 8614
Pravin Bhanushali Flat No. 2 Delhi 1641 11300 2034 13334
Pravin Mushtak Flat No. 9 Anand 1642 8400 1512 9912
Gayatri Mushtak Flat No. 1 Nagpur 1643 9200 1656 10856
Salman Shukla Flat No. 1 Nagpur 1644 6400 1152 7552
Gayatri Bhanushali Flat No. 4 Delhi 1645 7000 1260 8260
Gajodhar Mushtak Flat No. 3 Kolkata 1646 13800 2484 16284
Vivek Shukla Flat No. 7 Aurangabad 1647 11900 2142 14042
Gajodhar Shukla Flat No. 11 Kolkata 1648 5200 936 6136
Gayatri Lahoti Flat No. 6 Kolkata 1649 14800 2664 17464
Gayatri Khan Flat No. 2 Aurangabad 1650 6700 1206 7906
Vivek Mushtak Flat No. 2 Kolkata 1651 7300 1314 8614
Pravin Shashtri Flat No. 2 Aurangabad 1652 8900 1602 10502
Vivek Bhanushali Flat No. 10 Kolkata 1653 9800 1764 11564
Arjun Shukla Flat No. 13 Kolkata 1654 8800 1584 10384
Shahrukh Bhanushali Flat No. 6 Ajmer 1655 8400 1512 9912
John Tiwari Flat No. 9 Ajmer 1656 7900 1422 9322
Pravin Shukla Flat No. 12 Delhi 1657 7600 1368 8968
Sam Bhanushali Flat No. 2 Panaji 1658 13500 2430 15930
Salman Shashtri Flat No. 11 Jaipur 1659 12700 2286 14986
John Khan Flat No. 14 Solapur 1660 11100 1998 13098
Shahrukh Tiwari Flat No. 4 Delhi 1661 9500 1710 11210
Gayatri Shashtri Flat No. 7 Jaipur 1662 7100 1278 8378
Arjun Randad Flat No. 1 Panaji 1663 5400 972 6372
Shahrukh Khan Flat No. 7 Kolkata 1664 7500 1350 8850
Arjun Shashtri Flat No. 2 Ajmer 1665 14900 2682 17582
Arjun Randad Flat No. 14 Jaipur 1666 13200 2376 15576
Shahrukh Khan Flat No. 8 Ajmer 1667 8200 1476 9676
Salman Mushtak Flat No. 6 Anand 1668 9000 1620 10620
Gayatri Khan Flat No. 14 Delhi 1669 9800 1764 11564
Pravin Tiwari Flat No. 3 Ajmer 1670 7300 1314 8614
Vivek Lahoti Flat No. 15 Kolkata 1671 10400 1872 12272
Vivek Bhanushali Flat No. 11 Jaipur 1672 12600 2268 14868
Shahrukh Khan Flat No. 5 Aurangabad 1673 9900 1782 11682
Shahrukh Lahoti Flat No. 9 Delhi 1674 9000 1620 10620
Vivek Pande Flat No. 5 Solapur 1675 14600 2628 17228
John Randad Flat No. 14 Ajmer 1676 9500 1710 11210
Salman Tiwari Flat No. 14 Delhi 1677 9700 1746 11446
 Arjun Mushtak Flat No. 15 Nagpur 1678 11600 2088 13688
Shahrukh Lahoti Flat No. 13 Panaji 1679 6000 1080 7080
Arjun Tiwari Flat No. 7 Panaji 1680 9500 1710 11210
John Mushtak Flat No. 3 Anand 1681 6800 1224 8024
John Lahoti Flat No. 15 Jaipur 1682 14600 2628 17228
Sam Lahoti Flat No. 13 Aurangabad 1683 12700 2286 14986
Gayatri Mushtak Flat No. 12 Panaji 1684 10400 1872 12272
Vivek Mushtak Flat No. 9 Panaji 1685 12700 2286 14986
Vivek Randad Flat No. 1 Aurangabad 1686 9000 1620 10620
John Shukla Flat No. 12 Nagpur 1687 6300 1134 7434
Arjun Shashtri Flat No. 7 Panaji 1688 6000 1080 7080
Shahrukh Lahoti Flat No. 6 Delhi 1689 5700 1026 6726
Vivek Shashtri Flat No. 6 Anand 1690 13500 2430 15930
Gayatri Khan Flat No. 3 Delhi 1691 11500 2070 13570
Sam Mushtak Flat No. 8 Anand 1692 11100 1998 13098
John Bhanushali Flat No. 12 Anand 1693 9500 1710 11210
Shahrukh Khan Flat No. 14 Aurangabad 1694 5800 1044 6844
Sam Khan Flat No. 10 Anand 1695 13200 2376 15576
Gayatri Lahoti Flat No. 2 Nagpur 1696 8500 1530 10030
Gajodhar Randad Flat No. 14 Ajmer 1697 6600 1188 7788
Salman Shukla Flat No. 3 Delhi 1698 6300 1134 7434
Vivek Tiwari Flat No. 6 Kolkata 1699 10600 1908 12508
Arjun Pande Flat No. 11 Ajmer 1700 7400 1332 8732
 Reverse Lookup

Particulars Details
Bill No.
Customer Name
City
Address
Total
Amount
GST
 Eliminate Zero From
VLOOKUP
Employee City State Zip
Himesh Kolkatta WB 981147
Utsav Mumbai MH 981317
Shila Aurangabad MH 981335 Employee City State Zip
Samantha
George Deharadoon UK 981244
Narendra Nagpur MH 981162
Kedar
Samir Chennai TN 981143
Pankaj Hydrabad TS 981280
Ravi Jaipur RJ 981266
 Use of IFERROR with
VLOOKUP
Employee City State Zip
Himesh Kolkatta WB 981147
Utsav Mumbai MH 981317
Shila Aurangabad MH 981335 Employee City State
Samantha
George Deharadoon UK 981244
Narendra Nagpur MH 981162
Kedar
Samir Chennai TN 981143
Pankaj Hydrabad TS 981280
Ravi Jaipur RJ 981266
IFERROR with
LOOKUP

Zip
Particulars Details

What is name A name is a meaningful shorthand for a cell reference or a range or a table

Types of names Defined Names SalesQ1, name for cell or range

Table names Table 1, Table 2

Syntax Rules The first character of a name must be a letter, an underscore character (_),
or a backslash (\). Remaining characters in the name can be letters,
numbers, periods, and underscore characters.

Spaces not allowed

Same cell address not allowed
 Quick Formula Creation

Range Average
83 Sum
79 Count
28 Max
32 Min
94
75
19
40
52
28
46
50
71
94
53
94
93
15
10
83
58
85
62
16
32
87
15
29
27
26
31
61
90
69
18
76
70
31
10
60
14
15
28
37
22
69
59
63
52
26
33
32
91
12
27
39
95
88
80
65
56
87
29
11
23
50
25
59
87
88
17
71
58
25
16
74
50
66
88
65
80
37
60
83
34
73
16
98
26
69
70
62
11
92
37
22
59
50
41
32
81
Use of names to navigate
between Sheets
mes to navigate
een Sheets
Roll Number & Name Student Count Use of Wildc
3427 Sandy Hardy
1360 Roger Philip
Peter
combination wit
4676 Richard Philip
3657 Roger Joseph
7493 Richard Smith
5940 Roger Thomos Student Roll Number Count
1742 Richard Hardy John 8000-9000 11
1920 Roger Dawson
4002 Roger Philip
4711 Roger Smith
4090 Sandy Joseph
1857 John Philip
3557 Roger Smith
4414 Roger Philip
5500 Roger Dawson
7343 Larry Thomos
1011 Richard Hardy
8825 John Thomos
2418 Roger Hardy
7956 John Joseph
1296 Richard Hardy
7687 Peter Dawson
5229 John Joseph
5347 Larry Smith
3099 Larry Philip
5731 Roger Thomos
3209 Kevin Joseph
2868 Sandy Dawson
2769 Richard Joseph
4530 Kevin Joseph
6698 Roger Dawson
5534 John Philip
8210 Roger Hardy
8748 Sandy Dawson
3286 Kevin Dawson
6114 Larry Smith
7946 Kevin Thomos
6893 Kevin Joseph
2615 Larry Hardy
8545 Roger Thomos
4034 Kevin Hardy
4908 Larry Hardy
6399 Larry Hardy
8624 Kevin Smith
3329 Kevin Hardy
7057 Peter Hardy
3066 Larry Smith
1837 Richard Smith
2573 Sandy Smith
8660 Kevin Dawson
8227 Larry Hardy
8857 Richard Hardy
5142 Roger Dawson
3971 Kevin Thomos
7661 Sandy Hardy
3594 Kevin Smith
7377 Kevin Dawson
6989 John Thomos
2987 Richard Philip
6539 John Thomos
8944 Larry Hardy
3270 John Smith
5391 Kevin Philip
7625 Peter Thomos
1455 Peter Philip
1759 John Smith
1890 Richard Philip
8393 Roger Smith
3807 Richard Joseph
3194 Kevin Dawson
6598 Peter Hardy
7473 Larry Dawson
7115 Kevin Joseph
8489 Kevin Joseph
7539 John Joseph
6331 Roger Philip
1847 Peter Thomos
7995 Peter Thomos
8073 Larry Hardy
8547 Peter Hardy
1196 Larry Hardy
5079 Richard Smith
7167 Larry Smith
5027 John Dawson
5933 Kevin Dawson
4701 John Hardy
8281 Larry Dawson
7588 Kevin Hardy
3272 Peter Hardy
4611 John Thomos
4389 Kevin Philip
7652 Larry Philip
4098 Kevin Hardy
4515 Richard Dawson
1315 Kevin Hardy
1560 Sandy Hardy
2109 Richard Dawson
8019 John Joseph
5409 Sandy Philip
2657 John Smith
3596 Kevin Hardy
8733 Kevin Dawson
5289 Roger Philip
8508 John Dawson
7713 Larry Dawson
1110 Peter Smith
1122 Sandy Dawson
3905 Peter Smith
7998 Peter Smith
3553 Richard Joseph
7191 Kevin Smith
1990 Kevin Joseph
1515 John Dawson
8723 Larry Philip
6076 Sandy Joseph
6831 Peter Philip
1614 Sandy Smith
3209 Sandy Smith
5284 Roger Philip
6281 Peter Hardy
1559 Larry Philip
1944 Larry Thomos
3886 Sandy Thomos
6691 Kevin Smith
6947 Richard Smith
1698 John Dawson
7229 Sandy Joseph
1833 Richard Thomos
7958 Richard Philip
6665 Larry Philip
2001 John Joseph
6762 Peter Philip
2181 Kevin Dawson
4860 Sandy Philip
7437 John Philip
5963 Kevin Hardy
8356 John Smith
2353 Sandy Dawson
5163 Sandy Philip
2757 Roger Joseph
5690 Kevin Smith
2584 Larry Thomos
1883 Roger Thomos
8745 Roger Thomos
8504 Roger Joseph
5405 Peter Philip
3074 Sandy Hardy
6114 John Philip
8680 John Joseph
5821 Peter Smith
8951 Roger Dawson
3331 Peter Joseph
1230 Roger Thomos
5378 Roger Joseph
1161 John Thomos
1860 Kevin Smith
3316 Sandy Joseph
7747 Richard Smith
5152 Roger Hardy
3603 Kevin Dawson
5041 Richard Philip
6362 Richard Hardy
8471 Roger Thomos
6903 Roger Thomos
2483 Sandy Philip
4356 Kevin Smith
4162 Kevin Dawson
5364 Roger Smith
6865 Sandy Thomos
4225 Sandy Smith
5721 Richard Joseph
2529 Larry Hardy
3042 Richard Dawson
7694 Sandy Dawson
7603 Larry Hardy
4024 Kevin Hardy
4518 Peter Smith
4333 Larry Smith
4191 Richard Dawson
6524 Roger Joseph
2465 Richard Joseph
8256 Sandy Philip
3487 Roger Hardy
2731 Richard Joseph
6827 Larry Philip
6457 Richard Hardy
1494 Peter Philip
6063 Kevin Dawson
6575 Roger Dawson
5188 Kevin Smith
2719 Larry Smith
3516 Larry Thomos
6328 John Philip
6918 Richard Joseph
3942 Richard Joseph
1860 Larry Dawson
5056 Sandy Smith
2172 Richard Thomos
8928 Larry Smith
3031 Kevin Joseph
5192 Sandy Thomos
8836 Sandy Philip
5439 Sandy Smith
1126 Sandy Dawson
3689 Sandy Dawson
8956 Peter Philip
3692 Roger Philip
2630 Larry Hardy
7023 Roger Thomos
6957 Sandy Hardy
6774 John Philip
1671 Richard Philip
6157 Sandy Hardy
1154 Sandy Smith
7771 Larry Philip
6161 John Philip
8838 Larry Hardy
6960 Larry Joseph
3350 Sandy Hardy
3813 John Philip
8770 John Hardy
3259 Larry Philip
5267 Richard Joseph
3308 Larry Dawson
1419 Sandy Smith
7205 Richard Philip
3582 Kevin Smith
1902 Kevin Smith
3430 Kevin Hardy
7753 Roger Philip
3636 Peter Thomos
8552 Larry Joseph
7011 Peter Smith
8831 John Joseph
4206 John Smith
5426 Larry Thomos
7079 Roger Joseph
3197 Sandy Thomos
7074 John Philip
2454 Kevin Hardy
6746 Richard Smith
8038 John Philip
2828 Richard Hardy
7956 Richard Thomos
4401 Kevin Hardy
2245 Peter Dawson
3556 Larry Hardy
7139 Richard Smith
2886 Sandy Smith
7200 Peter Joseph
1320 Larry Dawson
2759 Richard Thomos
3407 Richard Smith
7631 Peter Philip
2073 John Smith
8603 Larry Joseph
6018 Sandy Dawson
1020 Peter Joseph
1284 John Smith
5376 Larry Philip
8087 Peter Hardy
7857 Roger Joseph
8229 Roger Philip
3160 Sandy Smith
1336 Kevin Philip
6865 John Joseph
7082 Richard Dawson
6936 Sandy Smith
3527 Larry Thomos
8518 Roger Smith
4397 Peter Thomos
5053 Richard Dawson
8611 Kevin Thomos
7847 Richard Joseph
5634 Kevin Philip
6132 Kevin Thomos
7356 Richard Philip
1875 Roger Dawson
8210 Richard Joseph
3462 Sandy Joseph
8408 Larry Joseph
2375 Kevin Philip
4276 Roger Philip
2077 Larry Thomos
4135 Peter Philip
6503 Kevin Thomos
3365 Larry Joseph
4491 Larry Joseph
1206 Kevin Dawson
4177 Larry Hardy
1159 Peter Philip
2736 Richard Philip
6667 John Dawson
2010 Roger Dawson
1118 John Joseph
7223 Peter Philip
6402 John Thomos
6904 Larry Joseph
4998 Peter Dawson
7449 John Dawson
5385 Roger Thomos
4005 Kevin Hardy
6786 Richard Joseph
5398 Kevin Philip
7286 Richard Philip
7294 Larry Philip
6202 Kevin Hardy
7019 Sandy Philip
6294 Kevin Thomos
4906 Sandy Joseph
4192 Larry Philip
8741 Larry Hardy
8769 Peter Hardy
6624 Sandy Thomos
2775 Peter Thomos
8601 Peter Hardy
8008 Roger Philip
5275 Richard Thomos
1234 Sandy Hardy
8385 Larry Joseph
8969 Roger Hardy
7911 Roger Joseph
6447 John Hardy
1014 Roger Philip
2575 Sandy Hardy
2100 Roger Smith
7798 Peter Smith
6560 Larry Smith
8159 Sandy Hardy
3736 Roger Joseph
5589 Larry Dawson
7194 Sandy Joseph
4964 John Thomos
1725 Peter Smith
5200 Larry Dawson
7811 Peter Philip
5221 Roger Smith
3245 Peter Philip
8319 Larry Dawson
1969 John Joseph
5912 Larry Smith
3232 Roger Hardy
5635 Larry Thomos
2349 Sandy Smith
2748 Sandy Smith
5084 Peter Dawson
1984 Richard Hardy
2245 Richard Dawson
4933 Richard Dawson
6269 Kevin Dawson
6611 Richard Dawson
4265 Peter Thomos
1885 Peter Hardy
8812 Kevin Joseph
4207 John Smith
4104 John Joseph
4979 Peter Hardy
8916 Kevin Dawson
6433 Richard Hardy
4121 Kevin Philip
3593 Roger Dawson
2861 Peter Thomos
4184 Kevin Philip
2753 Larry Dawson
3705 Kevin Smith
5451 Roger Thomos
3349 Peter Smith
4069 Larry Joseph
3069 Peter Smith
3066 Kevin Philip
1875 Sandy Joseph
2487 Roger Joseph
6004 John Smith
6106 Peter Dawson
7315 John Dawson
6575 Kevin Philip
2061 Roger Dawson
4221 Richard Smith
4574 Roger Hardy
6471 Roger Thomos
2773 Larry Philip
4378 Larry Dawson
8350 John Philip
4010 Larry Philip
2882 John Smith
6885 Roger Dawson
8129 Roger Thomos
7301 John Hardy
1216 John Dawson
3168 Richard Smith
2225 Larry Joseph
8343 Kevin Philip
6644 Roger Thomos
8603 Sandy Thomos
6722 Roger Joseph
5886 John Joseph
6268 Kevin Smith
2505 Peter Thomos
7599 Sandy Dawson
7420 Richard Smith
1922 Peter Joseph
3519 Larry Philip
3708 Richard Hardy
1604 Roger Hardy
8415 John Smith
4305 Peter Thomos
8323 Kevin Dawson
5874 Peter Thomos
8303 Peter Joseph
4085 John Philip
1700 Sandy Dawson
7108 Kevin Smith
2477 Larry Smith
2146 Richard Smith
1176 Kevin Smith
4606 John Thomos
2159 Larry Dawson
3166 John Dawson
6815 Peter Hardy
1784 Kevin Thomos
4412 John Joseph
8162 Peter Philip
4060 Kevin Joseph
6433 John Hardy
6063 Peter Hardy
4219 Richard Joseph
7915 Richard Philip
8478 John Joseph
7672 Richard Joseph
7001 Kevin Joseph
4557 Kevin Philip
8475 Peter Hardy
1824 Roger Thomos
8929 Peter Hardy
2394 Sandy Thomos
3426 Richard Thomos
3148 Richard Dawson
6485 Larry Joseph
4908 Peter Smith
1019 Larry Dawson
3360 Kevin Joseph
4098 Larry Thomos
7851 Richard Philip
4294 Roger Hardy
2655 Kevin Joseph
2772 Kevin Joseph
8678 Richard Joseph
4816 Larry Smith
8772 Peter Smith
3987 Sandy Smith
3222 Richard Smith
7537 Kevin Joseph
4709 Larry Philip
8513 Peter Philip
2646 John Thomos
3350 Roger Smith
3291 Richard Smith
5365 Larry Philip
6575 Sandy Joseph
2376 Larry Hardy
8611 Sandy Smith
7364 Roger Hardy
6525 Larry Joseph
6523 Kevin Thomos
7157 Kevin Hardy
1695 John Hardy
6628 Sandy Smith
3648 Larry Joseph
1761 Sandy Hardy
6272 Roger Joseph
2510 Kevin Dawson
1601 Peter Thomos
7411 Richard Smith
5116 Sandy Smith
1336 John Smith
6423 Roger Thomos
7382 John Smith
4544 Larry Thomos
8759 Sandy Joseph
8119 Larry Thomos
3936 Peter Thomos
2598 Kevin Philip
5023 Richard Joseph
6487 John Hardy
7894 Larry Hardy
1941 Larry Philip
2585 Sandy Dawson
5929 John Smith
7348 Kevin Dawson
5671 John Philip
7038 Sandy Philip
2983 Sandy Philip
2303 Larry Smith
5471 Roger Philip
8570 Roger Joseph
2622 John Thomos
6738 Peter Thomos
8370 Sandy Smith
7641 Sandy Joseph
6530 Sandy Joseph
 Use of Wildcard in
combination with names
Creating Names from Table Range

Jan Feb Mar Apr May Jun

Maruti 1433 9778 3430 5687 5634 3822
Toyota 3534 7729 9228 5453 6587 6020
Kia 8675 6788 9651 8630 3461 8968
Jaguar 4658 6206 5081 6621 3777 9583
Tata 9536 8467 3091 5353 7288 6492
Fiat 1584 8260 5489 5824 1373 2074
Hyundai 6553 8144 7158 9456 8558 4938
Volkswagen 5631 9364 8459 5361 8026 3241
 Define your own
formulas using names
 Use of Relative and
Sales Data Absolute Reference
Region Q1 Q2 Q3 Q4
North 1,282,274 1,011,204 1,020,143 1,391,067
South 1,354,780 1,497,895 1,154,242 1,440,176
East 1,345,728 1,041,662 1,455,801 1,191,942
West 1,210,569 1,067,038 1,052,787 1,115,863

Total 5,193,351 4,617,799 4,682,973 5,139,048

 of Relative and
olute Reference
Students Total Marks Results Dynamic Range using
John 10 Fail John names
Richard 7 Fail
Larry 9 Fail Student Marks Result
Sandy 26 Fail
Range using
mes
 Store Table in Memory
Sales Qty
North 85
East 25
West 76
South 87

North

West