MS Excel: Lookup instead of Vlookup or Hlookup

The limitation of vlookup is that you can only lookup in the first column of array and bring back results from it or any column right of it. 

Similar limitation in Hlookup where you can only lookup in first row of array and bring back result from it or any row below it.

To get over with this limitation, use lookup instead of vlookup and Hlookup:

=lookup(Lookup Value, Lookup Vector, Result Vector)

See the examples below:

Use of Lookup for Vlookup

Use of lookup for Hlookup

MS Excel : IfError function in Excel 2010

Iferror is new function in MS Excel 2010 to make life simple. It works as:

= IFERROR(value, value_if_error)

See it's use with vlookup below:

=Iferror(vlookup(A1,Sheet2!A1:C60, 3,False),”Not Found”)

In this formula, if vlookup returns error because value is not found, instead of returning  #N/A, it will return “Not Found”.

MS Excel: Show Alphabetic Row or Column Number

To show row number in alphabetic order, simply right this formula is first location where you wish to start the alphabet i.e. A and then, drag and paste the formula in rows below:

=SUBSTITUTE ((ADDRESS (1, ROW (A1), 4)), 1,"")

Same the case of alphabetical column numbers, at start point, write the following formula and drag and paste on adjacent columns:

=SUBSTITUTE ((ADDRESS (1, Column (A1), 4)), 1,"")

MS Excel: DATEDIF function

The DATEDIF function can be used to calculate the number of days/moths/years between two dates in versions of Excel from 97 to 2007.

=DATEDIF(B9,C9,"m")/12

=DATEDIF(B9,TODAY(),"d")

=DATEDIF(B9,C9,"m")

It is interesting to note that DATEDIF is not listed with other functions under the formula tab in Excel 2007.

Folder and File Option : Missing Security Tab: Windows XP

Problem: Right clicking on folder or file not showing security tab:


Go to Explorer -> tools -> folder options -> View->

navigate to the last check box in the list “USE SIMPLE FILE SHARING” .

Uncheck this checkbox and click OK.

Now you will be able to see the security tab in file properties.