The Vlookup function on Excel

• The Vlookup function (finding an exact match):

Building the function step by step:
1. Type the following code: =vlookup(
2. Type the address of the cell containing the value that you wish to look for in the table.
3. Type the range of the table to look inside, (or better: Name the table before starting with the function, and type now its name). Remember: don’t include the table’s heading row.
4. Type the column number from which you want to retrieve the result.
5. Type the word FALSE which means: “Please find me exactly the value of the cell mentioned in step 2. (Don’t round it down to the closest match)”.
6. Close the bracket and hit the [Enter] key.

Note: The function will always look for the value mentioned on step 2 only on the first column (the utmost left column) of the table.

Vlookup Examples:
Let’s assume you are using the following Excel worksheet: (You can download it here)

=vlookup(C10,A2:E7,2,FALSE)

In words: Look for the value in cell C10 inside table located at A2:E7, and retrieve me the value on the 2’nd column. Please find me exactly what’s in cell C10.

The value of the above formula will be: 99 (on Dan’s row, the second column).

Copying and replicating the function
If you plan on copying the vlookup function, either by “copy” and “paste” or by dragging it with the fill handle, make sure to set the table’s address with fixed reference (e.g. $A$2:$E$7), but it would be better instead to name the table, as in the following example.

You can name the range A2:E7 by selecting it, and typing the name in the name box.
Let’s assume you named it studentsTable (spaces are not allowed, but you may use underscores).

=vlookup(c10,studentsTable,3,false)

In words: Look for the value in cell C10 inside studentsTable, and retrieve the value from the table’s 3’rd column. Please find me exactly what’s in cell C10.

The value of the above function will be: 45 (on Dan’s row, the value in the third column of the table).

Troubleshoot (advanced usage with more functions)
If the vlookup doesn’t find a match, it will write the following code: #N/A.
You can overcome this code, and set what do display in case it doesn’t find a value by using the IF function with the ISNA() function. Look at the following example:

=if(isna(vlookup(c10,studentsTable,3,false)),”didn’t find any match”, vlookup(c10,studentsTable,3,false))

In words: if the vlookup function gives us the #N/A code, then write “didn’t find any match”, else compute the vlookup function.

Explanation:
The 3 parts of the above IF function are:
1. isna(vlookup(c10,studentsTable,3,false))
2. “didn’t find any match”
3. vlookup(c10,studentsTable,3,false)

The first part is a condition, which says: Does the vlookup function gives us the #N/A code?
If it does, write “didn’t find any match”, otherwise compute the vlookup function.

Tip:
Instead of the part “didn’t find any match”, you can put only double quotation marks “” which will leave the cell empty in case no match is found:

=if(isna(vlookup(c10,studentsTable,3,false)),””, vlookup(c10, studentsTable,3,false))

or let it retrieve the value 0 in case of no match:

=if(isna(vlookup(c10,studentsTable,3,false)),0, vlookup(c10, studentsTable,3,false))

• The Vlookup function - closest match

Building the function step by step:
This is exactly the same process as building the vlookup with exact match, but with the following difference: Instead of writing FALSE at the end of the function, write TRUE.

Example:
Let’s assume you have the following worksheet, and the table is named “sales_table”:

=vlookup(B10,sales_table,2,true)

In words: find me the value of cell B10 inside the first column of sales_table, and retrieve me the value next to it from the table’s second column. If you don’t find the value, relate to the closest smaller value you can find.

Hence, the value 14 doesn’t appear on the first column of the table, so the function will relate to the value 10 which is the closest smaller value, and retrieve the word “medium” from the second column.

If the value of cell B10 was changed to 90, the vlookup would return us the word “Great” (the closest smaller match it finds would be 50).

• Getting Rid of the Vlookup #N/A Error Code

 

When the vlookup function doesn’t find the value inside the range specified, it will write the #N/A code, meaning “Not Available”.

To get rid of this error code, you can put the vlookup inside an IF function together with the ISNA function in the following format:

=If (isna (vlookup(blabla)) , “”, vlookup(blabla))

In words:
If the vlookup function is N/A, then leave the cell blank, otherwise perform the vlookup function.

(It is recommended that you be familiar with the If Statement before you proceed.)


An Example (taken from the related video):

=If(isna(vlookup(D9,oldList,2,false)),””,vlookup(D9,oldList,2,false))

(Note the double parenthesis after the word “false” in both of its occurrences)

Let’s cut the above function into three parts, and examine them one by one:

First part:

=If(isna(vlookup(D9,oldList,2,false)),

In words:
If the function vlookup(D9,oldList,2,false) gives N/A - (this will happen if it didn’t find the value of cell D9 inside oldList),


Second part:

“”,

In words:
Then leave the cell blank (double quotation marks mean “blank”)

Third part:

vlookup(D9,oldList,2,false))

In words:
Otherwise perform the vlookup as usual.

 

E-filing of Service Tax Returns (ST-3)

E-filing of Service Tax Returns (ST-3) made mandatory for All Assessees wef 01.10.2011 vide Notification No. 43/2011 - Service Tax, dated 25.08.2011 (click here for Notification)

E-filing of Central Excise Returns

E-filing of Central Excise Returns (ER1,ER-2,ER-3,ER-4,ER-5,ER-6, ER-7 & ER-8) made mandatory for All Assessees wef 01.10.2011 vide Notification No. 21/2011 and No. 22/2011 - Central Excise, both dated 14.09.2011 (click here for Notification No. 21/2011)    (click here for Notification No. 22/2011)



here is the link for downloading(ER1,ER-2,ER-3,ER-4,ER-5,ER-6, ER-7 & ER-8) files
(http://www.aces.gov.in/download.jsp)

GAR7 Challans Click on the respective hyperlink to down-load GAR7 Challans. - Central Excise ( Front || Reverse ) - Service Tax ( Front || Reverse )

Central Excise Bangalore Contact information

Office of the Commissioner of Central Excise
Zone	ADDRESS	Phone No.	Fax No.	Email ID
Bangalore - I	P.B. No. 5400, Central Revenue Building, Queens Road, Bangalore - 560001.	+91-80-2286 4073	+91-80-2286 4170	cexbang@excise.nic.in
Bangalore - II	P.B. No. 5400, Central Revenue Building, Queens Road, Bangalore - 560001.	+91-80-2286 5331	+91-80-2286 4426	cexbang2@excise.nic.in
Bangalore - III	P.B. No. 5400, Central Revenue Building, Queens Road, Bangalore - 560001.	+91-80-2286 7733	+91-80-2286 0354	cexbang3@excise.nic.in

ELECTRONIC ACCOUNTING SYSTEM IN EXCISE AND SERVICE TAX

Introduction

Central Board of Excise and Customs (CBEC) initiate to receive information and maintain records of tax paid through banks through online upload of challan details is named as EASIEST ( Electronic Accounting System in Excise and Service Tax ).

Data uploaded by banks

CBEC has devised the file formats for uploading data regarding tax payment. Banks are expected to generate and upload tax data as per these formats.

Once the file has been prepared as per the file format, it can be verified for correctness of its structure using the File Validation Utility (FVU) provided by NSDL.

Single copy challan for excise and service tax

From April 2007 onwards assessees should pay tax using the single copy challan, GAR-7 instead of earlier four copies.

• GAR-7 - Proforma for Central Excise Tax Payments is to be used for payment of Excise and
• GAR-7 - Proforma for Service Tax Payments is to be used for payment of Service Tax.

Challan status enquiry

Assessees can track online the status of their challans deposited in banks. The status can be tracked on the basis of the Challan Identification Number (CIN) which is stamped on the challan counterfoil by the bank. CIN comprises of seven-digit BSR code of collecting bank branch, challan serial number (upto five digits) and date of tender.

Assessee Code based search

This facility enables the assessee view its details (name, address, location code) as present in the Assessee Master provided by CBEC. Details of assessee codes allotted for service tax and excise can be viewed using this facility.

VAT Refund In SEZ (Karnataka)

Normally there would be zero rating or exemption provided for supplies to SEZ. IN case they have paid the VAT for some reason the same would certainly be refundable.

Retention Amount

Friends, treatment of retention money in both of scenraios will depend upon the underlying agreement. Typically renetntion money is 10 percent (which can be less or more than it) of the contract value and is dedcuted from all the payments to be made to the contractors against the certified work. Retention money is a credit balance payable to contractor upon finalization of satisfied completion and performance of work. It is normally paid to contractor after one year of completion of contract work. However, this will again depend upon agreed terms.

If the contractor does not complete the work or the work performed by him is not satisfactory, the retention money may not be paid to him depending upon agreed terms. In such case, it can be kept as a liability in some "sundry liabilities" account and added back to profit and loss account as other income. Normally people keep the liability for 3 years and then add it back as income. This is done so that no issue arises in taxation as tax laws require adding back after 3 years. Other recoveries, damages or legal proceedings, or arbitration etc, everything will also depend upon the agreement and then legal rights under the specific circumstances.

If the work done is satisfactory, as per requirement, is certified and completed, the amount retained as retention money will keep on standing as liability in the books of account until the payment date AS PER AGREED TERM arrives. On which date the liability will be paid to the contractor. Remember, amount to be retained, retention period, payment methodologies, all dependd upon agreement with contractor.