Formula/Shortcuts Usage Result
- Concatenate to combine two different values
- Today to get current date 8/9/2024
- CTRL + : FOR CURRENT DATE 9/19/2022
- Now to get current date with time 8/9/2024 8:51
- CTRL (+) Shift (+) : FOR CURRENT TIME 12:33 PM
CTRL (+) Shift (+) $: For currency format ₹ 10.00
CTRL (+) K To add hyperink
Clock
|||||||| 08
||||||||||||||||||||||||||||||||||||||||||||||||
|||||||||||||||||||||||||||||||||||||| 38
||||||||||||||||||||||||||||||||||||||||||||||||||| 51
|||||||||||||||||||||||||||||||||||||| 38
1 V-lookup Vertical lookup
We can use the VLOOKUP function with the help of a simple syntax. The Syntax for VLOOKUP is:
Syntax vlookup(lookup value,table area,column number,range lookup)
Lookup value The value against which we want to get data
table area Source data from where we want to find the lookup value
Column number address of coulmn number in table area which we want against lookup value
Range lookup type "True" to get approximate match and type "false" to get excat match
Example Output
Fruit Month Sale Fruit
Mango Apr-22 10 Grapes
Apple May-22 20
Orange Jun-22 20 Syntax explain: (Formula is in
Grapes Jul-22 10 Lookup value
table area
Column number
Range lookup
What is VLOOKUP?
VLOOKUP stands for Vertical Lookup. As the name specifies, VLOOKUP is a built-in function and very easy &
vlookup(lookup value,table area,column number,range lookup)
Where :
Lookup value : the value against which we want to get data
Table area : source data from where we want to find the lookup value
Column number : address of coulmn number in table area which we want against lookup value
Range lookup : type "True" to get approximate match and type "false" to get excat match
Tips
If you omit range_lookup from the VLOOKUP function, it will evaluate the non-exact match
If the lookup column has duplicate values available then VLOOKUP will only pick the first
VLOOKUP is not case-sensitive.
Adding a column after applying VLOOKUP formula will show an error as hard-coded index
Common Errors in VLOOKUP Function
#N/A! error – Occurs if the VLOOKUP function fails to find a match to the supplied lookup_
#REF! error – Occurs if when the col_index_num argument > number of columns in the su
#VALUE! error – Occurs if either: The col_index_num argument is less than 1 or is not re
This is all you need to know about VLOOKUP in Excel.
Clock
|||||||| 08
||||||||||||||||||||||||||||||||||||||||||||||||
tax for VLOOKUP is: |||||||||||||||||||||||||||||||||||||| 38
against lookup value
o get excat match
Month Sale
01.07.2022 10
Syntax explain: (Formula is in H14)
Lookup value The cell address of the look up value, here its G14 (Grapes)
Select total area of the table from where we need to find the value (B13 to D17)
Column number add column number of the table which we want find against the G14 (For Month is column 2 & For sale its 3)
Range lookup type "false" to get the excat match
uilt-in function and very easy & useful tool in Excel that helps you look for a specified value by searching for it vertically across the sheet.
gainst lookup value
t excat match
aluate the non-exact matches as well. However, it will use an exact match if it exists in your data.
KUP will only pick the first value
n error as hard-coded index values don’t change automatically on adding or deleting columns.
atch to the supplied lookup_value.
umber of columns in the supplied table_array; or the formula attempts to reference cells that do not exist.
nt is less than 1 or is not recognized as a numeric value; or The range_lookup argument is not recognized as one of
||||||||||||||||||||||||||||||||||||||||||||||||||| 51
|||||||||||||||||||||||||||||||||||||| 38
th is column 2 & For sale its 3)
or it vertically across the sheet.
that do not exist.
s not recognized as one of the logical values TRUE or FALSE.
2 H-lookup Horizontal looup
Syntax vlookup(lookup value,table area,row number,range lookup)
Lookup value The value against which we want to get data
table area Source data from where we want to find the lookup value
Row number address of row number in table area which we want against lookup value
Range lookup type "True" to get approximate match and type "false" to get excat match
Note:-
This formula is same as "VLOOKUP"
Only difference is Vlookup works to find the data from vertical format and Hlookup find
Clock
|||||||| 08
||||||||||||||||||||||||||||||||||||||||||||||||
|||||||||||||||||||||||||||||||||||||| 38
t against lookup value
se" to get excat match
om vertical format and Hlookup finds data from hozontal format
||||||||||||||||||||||||||||||||||||||||||||||||||| 51
|||||||||||||||||||||||||||||||||||||| 38
3 if Conditional Formula
Syntax If(Logical Test,Value if true,value if False)
Logical test Logical value. i.e is equal to, is greter then, is less then etc….
Value If True Value if logics satisfied
Value If False Value if logic not satisfied
Name Marks Result <- output
A 35 Pass
B 25 Fail
C 40 Pass
D 42 Pass
E 78 Pass
F 30 Fail
Syntax explain: (Formula is in D10)
Logical test Above 35 marks
Value If True Pass
Value If False Fail
Clock
|||||||| 08
||||||||||||||||||||||||||||||||||||||||||||||||
|||||||||||||||||||||||||||||||||||||| 38
||||||||||||||||||||||||||||||||||||||||||||||||||| 51
|||||||||||||||||||||||||||||||||||||| 38
4 Sumif Sum the cells which meets one condition
sumif(range,critaria,sum range)
5 Sumifs Sum the cells which meets saveral criteria
sumifs(sum rage, range1,critaria1,range2,critaria2,)
Example :
Product Month Qty
Mango Apr-22 10
Apple May-22 20
Orange Apr-22 10
Mango May-22 20
Apple Apr-22 10
Orange May-22 20
Sumif Syntax explain: sumif(range,critaria,sum ran
Sumif ---> Product Qty <-- output Range
Mango 30 Cirtaria
Will Get Orange's total sales as per putput Sum range
Sumif Sumifs
Sumifs ---> Product Category Qty Syntax explain: sumifs(sum rage, range1,crit
Orange Apr-22 10 Sum range
Range1
We will get answer for Orange's sale in April-22 as Cirtaria1
per output Range2
Cirtaria2
Difference beetwen sumif & sumifs
SUMIF
When we want to get sum with only 1 critaria (Example-1)
SUMIFS
When we want tp get sum with more then 1 critaria (Example-2)
Clock
|||||||| 08
||||||||||||||||||||||||||||||||||||||||||||||||
|||||||||||||||||||||||||||||||||||||| 38
plain: sumif(range,critaria,sum range) Formula is in D18
select the column from table in which the value is mentioned whch we want to find
Select the cell address where the value is mentioned against which we want to get the result
select the column from the table in which value is mentioned
plain: sumifs(sum rage, range1,critaria1,range2,critaria2,) Formula is in D23
select the column from the table in which value is mentioned
select the column from table in which the value is mentioned whch we want to find
Select the cell address where the value is mentioned against which we want to get the result
select the column from table in which the value is mentioned whch we want to find
Select the cell address where the value is mentioned against which we want to get the result
||||||||||||||||||||||||||||||||||||||||||||||||||| 51
|||||||||||||||||||||||||||||||||||||| 38
4 Countif Count the cells which meets one condition
countif(range,critaria)
5 Countifs Count the cells which meets saveral criteria
countif(critaria_range1,critaria1,critaria_range2,critaria2)
Product Month Qty
Mango Apr-22 10
Apple May-22 20
Orange Apr-22 10
Mango May-22 20
Apple Apr-22 10
Orange May-22 20
Countif
Countif Product Qty Syntax explain: countif(range,critaria) Formu
Orange 2 Range
Will Get Orange's total count as per putput Cirtaria
Countif Countifs
Countifs Product Category Qty
Orange Apr-22 1 Syntax explain: countif(critaria_range1,crita
We will get answer for Orange's count in April-22 Range1
as per output Cirtaria1
Range2
Difference beetwen countif & countifs Cirtaria2
countIF
When we want to get count with only 1 critaria (Example-1)
countIFS
When we want to get count with more then 1 critaria (Example-2)
Clock
|||||||| 08
||||||||||||||||||||||||||||||||||||||||||||||||
|||||||||||||||||||||||||||||||||||||| 38
plain: countif(range,critaria) Formula is in C17
select the column from table in which the value is mentioned which we want to find
Select the cell address where the value is mentioned against which we want to get the result
plain: countif(critaria_range1,critaria1,critaria_range2,critaria2) Formula is in D22
select the column from table in which the value is mentioned which we want to find
Select the cell address where the value is mentioned against which we want to get the result
select the column from table in which the value is mentioned which we want to find
Select the cell address where the value is mentioned against which we want to get the result
||||||||||||||||||||||||||||||||||||||||||||||||||| 51
|||||||||||||||||||||||||||||||||||||| 38
6 Datedif This formula helps to get the difference beetween two days
We can get output in terms of:
Syntax:
In Days DATEDIF(start date,end date/current date,"d")&"-days"
In Months DATEDIF(start date,end date/current date,"m")&"-months"
In years DATEDIF(start date,end date/current date,"y")&"-years"
Combine DATEDIF(start date,end date/current date,"y")&"-years,"&DATEDIF(st
- In Days
Start Date Current Date Output
1/1/2000 8/9/2024 8987-days
1/1/2010 8/9/2024 5334-days
- In Months
Start Date Current Date Output
1/1/2000 8/9/2024 295-months
1/1/2010 8/9/2024 175-months
- In Years
Start Date Current Date Output
1/1/2000 8/9/2024 24-years
1/1/2010 8/9/2024 14-years
- Total Combine
Start Date Current Date Output
1/1/2000 8/9/2024 24-years,7-months,8-days
1/1/2010 8/9/2024 14-years,7-months,8-days
1/1/2020 8/9/2024 4-years,7-months,8-days
1/1/2023 8/9/2024 1-years,7-months,8-days
e,"d")&"-days"
e,"m")&"-months"
e,"y")&"-years"
e,"y")&"-years,"&DATEDIF(start date,end date/current date,"ym")&"-months,"&DATEDIF(start date,end date/current date,"md")&"-days"
e/current date,"md")&"-days"
