Using VLOOKUP on Excel

Using VLOOKUP on Excel-Explained with Syntax and Examples

VLOOKUP functions of excel is very useful when you need to return a value from a table or a range of cells by looking up another value. VLOOKUP on excel can be used for finding the value in a range which are present in the other range in the same sheet or other sheet and other excel files. Let is quickly look into the syntax of vlookup function and examples on how to use VLOOKUP on Excel.  Do note than simple VLOOKUP function works with only single criteria. If you are looking for 2 or more criteria vlookup, the please refer my previous post multi criteria vlookup.

Syntax of Excel VLOOKUP Function:

= VLOOKUP( value, table_array, index_number, Typeoflookup )

where

Value: is the value to search for in the first column of the table_array or range of cells. Value can either a text or a numerical value.

table_array: is the range of cells where you need to lookup for the value.

index_number: is the column number in table_array from which the matching value must be returned. A index_number argument of 1 returns the value in the first column in table_array; a index_number of 2 returns the value in the second column in table_array, and so on.

Typeoflookup: This is not mandatory. However this will chose between exact match and relative match.  If you use TRUE or if you omit this argument, then non exact match lookup will be performed. If this is set to FALSE, then vlookup does the exact match

Note and Troubleshooting Tips on Excel:

• If  "Typeoflookup" is either TRUE or omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.
• If "Typeoflookup" is FALSE, the values in the first column of table_array do not need to be sorted.
• Excel vlookup returns #VALUE! if  "index_number" is less than 1 and returns  #REF! if the  "index_number"Greater than the number of columns in table_array. Also one more error value comes with VLOOKUP is #N/A. Excel returns #N/A when there is no exact match or the value you are matching in the range is not found.
•  If there are two or more values in the first column of table_array that match the lookup_value, the first value found will be returned

Example on Using VLOOKUP formula in Excel:

Sample data

Using vlookup with Exact match: Considering above sample data, if you need to lookup for the value "Banana" and fetch the Rating, use the formula given below.

=VLOOKUP("Banana",A2:C6,3,FALSE) which returns C.

Using vlookup with non Exact match: This is useful, when you need relative value. Say for example, your matching value based on the "% of commission" and you want to return the ratings. Here do not that, for using non exact match the first column of lookup range  must be placed in ascending sort order.

=VLOOKUP(33%,B2:C6,2,TRUE) which returns C. Here first instance of nearby value is taken by the formula.

Formatting the VLOOKUP on Excel using ISNA and IF functions:  When there is no match found, excel returns #n/a.  This is will give problems when you need to use SUM function on the return value of returned value.To overcome this, we need to use ISERROR function. This very useful, if you are creating a template with VLOOKUP function.

Examples:

From the same above sample table, we will match Strawberry. The formula returns #n/a as the match is not found on the table. 

=IFERROR(VLOOKUP("Strawberry",A2:C6,3,FALSE),"Not Found") which   returns "Not found".

The IFERROR functions syntax is =IFERROR(Value, value if error) where "value" is the data which needs to checked for error and "value for error" is the value to be returned incase of error.

In above example we use VLOOKUP return data as value and "Not Found" used if the returned value is #N/A.

IFERROR function was introduced in Excel 2007. Hence if you are using earlier version of Excel 2007, then you need to use IF function and ISERROR function of Excel. So the formula is as shown below.

=IF(ISERROR(VLOOKUP("Strawberry",A1:C6,3,FALSE)),"Not Found",VLOOKUP("Strawberry",A1:C6,3,FALSE)) 

Note: Do not use this function unless you have earlier versions of excel as this will lead to calculation time. This is because for every time you use the function, excel needs to calculate vlookup twice. when range to lookup is more and number of instances of using this formula more, then excel will take really long time to calculate and return the right values.
Please do share your feedback and tips which you know in the comment section below. If you need any specific support on Excel, then mention your query in comment section, I will try to answer your query.

How To Send Data From Flash To ASP/PHP Without A Page Refresh

      This article will deal the how to of sending form data from Adobe Flash to your PHP/ASP file so it can be inserted into a database (or manipulated for whatever other reason). The trick is making sure a new window doesn’t popup and the page doesn’t refresh to run through the ASP/PHP code.

How To Do a CSS PopUp Without Opening a New Window

     

      Ever wonder how some people can get little CSS PopUp’s without opening an annoying window? I’ll explain how. One of its advantages is that it’s not blocked by blockers and you can place it wherever you like on your page. Apart from the short Javascript file that you don’t even have to look at, it’s quite easy to understand.

CSSPopUp.js file.

function toggle(div_id) {
	var el = document.getElementById(div_id);
	if ( el.style.display == 'none' ) {	el.style.display = 'block';}
	else {el.style.display = 'none';}

Centering a pop-up window...

#PopUpFad {
	background: #f2f2f2;
        text-align: center;
	font-family: Arial,sans-serif;
	padding: 10px;

Some Free webhosting websites

http://www.htmlcodetutorial.com/comments/viewforum.php?f=42&sid=8a4b58825a2ab3160197bceeb495bb89

http://www.jabry.com

http://www.greatspace.org/

http://www.jabry.net/

http://www.webworldguru.com/

http://www.hpage.com/