Tag Archives: function

Microsoft Excel VLOOKUP function Explained

Posted by on January 10, 2012 No comments

by A.R. Vital

Vlookup:

As the name it means it lookups (Search). Vlookup is one of the most important and useful function in Excel. It extracts a single record by searching a value which you want in the given dump and extract the data from the concerned column (which you specify). This function has 4 fields. The syntax is

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Lookup_value:

The value which you want to lookup (search)

For example:   Cell reference like A1, B1 (i.e. Cell A1, Cell A2) or Column reference like A:A, B:B (I.e the entire column) or referring to a single cell by making it as constant like $A$1 (i.e. searching the same value/cell in different arrays/dump)

Table_Array:

Simply the dump. But it should start with the Lookup_value, i.e. if you are searching for “Apple”, the first column in the dump should contain “Apple” (it may be in any row).

For example: Cell reference like A1:E10/A:E or to another sheet in same workbook (will be in the form – sheetname!{reference} like Address!A:B) or a sheet in another workbook (will be in the form – ‘[File name.xls]Sheetname’!{reference} like ‘[Courier Dispatch Feb-2010.xls]Sheet1′!$A:$B)

Col_index_num:

Numbers like 1,2,3…. The column number from where you want to extract the data.
For example: In the above example (Table_Array) from the cell reference A1:E10 (consists A1:A10, B1:B10, C1:C10, D1:D10, E1:E10), if you want to extract the data from the first column i.e. Column A, you have to give the number as 1, likewise if you want to extract the data from the Column C, you have to give the number as 3.
If the Dump (Table_Array) starts from the column Z like Z1:AE10/Z:AE and if you want to extract the data from the first column i.e. Column Z, you have to give the number as 1 only because the given dump starts from Z Column and it is the first column in the dump, likewise if you want to extract the data from the Column AC, you have to give the number as 4.

[range_lookup]:

By defining this with 0/False (means exact search i.e. it searches only for the value which you give it in Lookup_value) or 1/True (means search approximately equal to or less than the value i.e. if it is searching for the value like “1.026”, if “1.026” is not available and “1.025” is available, excel considers 1.026=1.025). If you leave this field blank, Excel compiler automatically consider it as 0/False.

So the entire Vlookup function will be in the form
Referring in the same sheet

=VLOOKUP(A:A,G:I,3,0)

Referring to other sheet in same workbook

=VLOOKUP(A:A,Sheet2!A:B,1,0)

Referring to a sheet in another open workbook

=VLOOKUP(A1,'[Courier Dispatch Feb-2010.xls]Sheet1'!$A$1:$B$65536,1,0)

Referring to a sheet in another closed workbook

=VLOOKUP(A1,'E:\Reports to be sent\[Courier Dispatch Feb-2010.xls]Sheet1'!$A$1:$B$65536,1,0)


Share|

Microsoft Excel If and Nested If function examples

Posted by on January 10, 2012 No comments

In this short tutorial, I explain how to use If function and Nested If function in Microsoft Excel with an example

It is very easy to implement If THEN ELSE logic in Microsoft Excel

Let’s take a look in detail. Let us see the syntax first

Simple If Syntax function

IF( condition1, value_if_true1,value_if_false1 )

This is equivalent to saying….

IF condition1 THEN
        value_if_true1
    ELSE
        value_if_false1

Nested If Syntax function

IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))

This syntax is equivalent to…

IF condition1 THEN
        value_if_true1
    ELSEIF condition2 THEN
        value_if_true2
    ELSE
        value_if_false2
    END IF

Now, Let’s take closure look into an example

Please pay an attention to the above figure- showing If, Nested If functions

Q1: This column using a simple If

Requirement: display “A” for Customers whose Ids are less than 200 Otherwise display “B” in the Q1 column

We write below a simple if to fulfill this requirement

IF( A2 < 200, "A","B")

Please refer to the Q1 column in the above figure on the output of this function

Q2: This column using a Nested If

Requirement:

if ( Customer IDs < 200 ) then
   return "A"
 else if ( Total Points > 100 ) then
   return "A"
 else
   return "B"

We write below a Nested if to fulfill this requirement

IF( A2 < 200, "A",IF ( D2 > 100, "A","B") )

Please refer to the Q2 column in the above figure on the output of this function

How do we write If ( A > 10 and B > 20 )?

Yes. In Microsoft excel, you could do multiple conditions in If. You use the keywords ‘AND’, ‘OR’ in IF block to add more conditional statements.

AND : Used in the cases where more than one condition should get satisfied to execute the true block otherwise it will execute false block
OR : Used where at least one condition statement should to get satisfied to execute the true block otherwise it’ll execute the false block

Q3: ‘AND’ Keyword in IF block, example

Requirement: Display “Eligible” for the customers whose points are > 50 and their order price is greater than 100. i.e.,

If (Total points > 50 and Price > 100 ) then
             display "Eligible"
        else 
             display "Not Eligible"

IF ( AND ( D2 > 50, C2 > 100 ), "Eligible", "Not Eligible" )

Please refer to the Q3 column in the above figure on the output of this function

That’s it. We’ve reached the end of the tutorial on ‘How to use If and Nested If functions in Microsoft Excel’

Do what you love! But To be a Techy, do more reading

Share|