VLookup
=VLOOKUP(A7, A2:B5, 2, FALSE).
Tip 1. Make sure the main column you want to match data to is in Ascending order. Same with the main column you are retrieving data from. In this example it would be A2
Tip 2. Better to have a separate spreadsheet with the data you want to get the information from rather than have it all on one sheet.
Tip 3. False means an exact match. True means an approximate match.
Tip 4. Of Course, when using this formula, make sure you change the column letter(s) to reflect your column
Formula to change a date to the month
=TEXT(C2, "mmmm")
Tip 1. This formula changes the month inside a formula to the text month of that date .
Tip 2. For example: 10/13/2020 just becomes "October"
Tip 3. Just drag the formula down the right side of the column with the dates you want to change.
Formula to calculate difference in years between dates
=DATEDIF (A2,B2, "Y")
Tip 1. Put this formula in a third column for the results
Tip 2. An example of how this formula works: If the Start date of 01/01/2015 is in A2 and the end date of 05/04/2017 then the result would be 2 (for 2 years).
Formula to calculate date difference in months
=DATEDIF (A2,B2, "m")
Tip 1. Put this formula in a third column for the results
Tip 2. This formula is similar to the "Y" formula above. However, if the Start date of 01/01/2015 is in A2 and the end date of 05/04/2017 then the result would be 28 (for 28 months).
Formula to calculate years, months and days between dates
=DATEDIF(A1,B1,"y")&" years"&", "&DATEDIF(A1,B1,"ym")&" months"&", "&DATEDIF(A1,B1,"md")&" days""
Tip 1. Put this formula in a third column for the results
Tip 2. This formula is basically a combination of the formulas from above. However, if the Start date of 01/01/2015 is in A1 and the end date of 05/04/2017 is in B1, then the result would be 2 years, 4 months, 3 days.
Formula to Find Duplicates
=(COUNTIFS($N$2:N2,N2)>=2)+0
Tip 1. This formula searches for duplicate values based on a row you select.
Tip 2. Add this value to the second row next to the column you want to find dups.
Tip 3. Change the alphabet to their respective columns you want to search
Tip 4. Right click and drag the handle of the cell with the formula down the sheet until all rows have been captured.
Concatenate Formula to Add Text cells together
=A1&” another cell”
For example: If A1 has "John" and D1 has "Doe", then the formula =A1 & D1 will result in "John Doe"
MID Function retrieves the middle contents of a cell
=MID(A1,6,3)
From a starting point within a cell, MID returns text left to right
For example, "John Doe Jr" in cell A1 will retrieve "Doe" in whatever cell the formula is placed.
RIGHT Function retrieves the right contents of a cell
=RIGHT(A1,2)
From a starting point within a cell, RIGHT retrieves the contents after a comma
For example, "San Fran, CA" in cell A1 will retrieve "CA" in whatever cell the formula is placed.
Left Function to remove the left most word in a cell
=Left(A1,4)
For example: "John Doe" in cell A1 will move just the "John" with this function: =Left (A1, 4) in another cell
IF
=IF(C2=”Yes”,1,2)
Tip 1. Checks the value of what you have in a cell. In this example if the cell says "Yes" then another cell would pop up with "1" as it's value. If cell C2 doesn't have "Yes" then another cell would show "2".
Given this Excel table below:
Cde County ID IdnCase Applicant Last Name Applicant First Name Address1 Address2 City State Zip
Given this Excel table below:
Given this Excel table below: