Simple VLookup

This function is used to look-up a value in a specific column and return any corresponding value.

Syntax :

=VLOOKUP(value to look for, range to look in, number of column of to return, whether exact match)

For Example:

=VLOOKUP(A1,D:E,2,FALSE)

This will search for value in A1 in column D and will return the corresponding value in column E.

VLOOKUP can also be used to compare two columns. This function can be applied to a column with more values so that it returns #N/A in the values for which it doesn't find a match for.

VLookUp single value in multiple sheets

=IF(ISNA(VLOOKUP(D2,Added!A:A,1,FALSE)),
IF(ISNA(VLOOKUP(D2,'Not Added'!A:A,1,FALSE)),
IF(ISNA(VLOOKUP(D2,Exisitng!A:A,1,FALSE)),
"Not Found","Existing"),
"Not Added"),
"Added")

Where

-- "Added", "Not Added" and "Existing" are sheet names.
-- Above formula is searching for value in D2 in all the three sheets and returning the specified values.

Word Count in a Cell or a Range of Cells

Use the below formula to count the number of words in a particular cell:

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+COUNTA(A1)

This will count the no. of words in cell A1.

Use the below formula to count the number of words in a range of cells:

=LEN(TRIM(A1&A2&A3&A4&A5))-LEN(SUBSTITUTE(A1&A2&A3&A4&A5," ",""))+COUNTA(A1:A5)

This will count the no. of words in cells A1 to A5.

Data List Validation

Use the below procedure if you want to limit the data values enetered
in a column.

(User will select the value from a dropdown)

* List down the unique values to a different column.
* Select the complete column where the user will enter the values.
* Goto Data --> Data Validation
* In "Allow" dropdown, select "List"
* Click in the "Source" and select the range of the unique values.
* Click OK.

Workbook Tab Count

Follow the below procedure to count the number of Tabs in the workbook.

* Go to the code in "ThisWorkBook". You can find the code by

-- pressing ALT+F11

-- Right-clicking on "ThisWorkBook" on the left

-- Left clicking on view code.

Then paste in the following code:

Private Sub Workbook_Open()
Sheets("NameOfSheet").Cells(1, 1).Value = Worksheets.Count
End Sub

* Save the code and close.
* Save the file as “Excel Macro Enabled Workbook (.xlsm)” and close.

When the worksheet opens, it'll count the number of worksheets and put
the value in a specified cell. So in this case if you have a sheet
named NameOfSheet it will put a number in Row 1, Column A.

Note: If it doesn’t show the count after opening the sheet, look for
the security warning on the left top which says “Macros have been
disabled.”

Solution:

* Click on Options
* Select “Enable this content”
* Click “OK”

Date Difference

Use the below formula to get the difference between two dates in :

=DATEDIF( Date1, Date2, OutputRequirement)

OutputRequirement Codes :

Code		Description		Explanation
d		Days		The number of days between Date1 and Date2.
m		Months		The number of complete months between Date1 and Date2.
y		Years		The number of complete years between Date1 and Date2.
yd		Days excluding Years		The number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.
ym		Months excluding Years		The number of months between Date1 and Date2, as if Date1 and Date2 were in the same year.
md		Days excluding Months and Years		The number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and the same year.

Intro...

Check out this place for regular TIPS 'n' TRICKS in Excel. You can also post your comments, questions & problems in the comments area and I'll try provide a resolution for that.

Keep Excel-ing ;)