Friday, February 28, 2014
When you open csv file in MS Excel, for any text field as number with preceding Zero, excel treats them as number and remove the preceding Zero.
Let us say, that field is 5 characters long:
Excel will open it as (Assume in cell A1):
You can use the following formula to get back the Zero:
See the example below:
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
Thursday, February 27, 2014
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”.
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,"")