MS EXCEL TRAINING | EXCELWITHEASE.COM

4 comments

“Today in the time of technology where everyone use computer and MS excel in routine office work but still doesn't know how to reduce the work using the amazing shortcuts of MS excel and its some unbeatable commands"

 Hi Guys!!!

Today all of us are familiar with computer and MS Excel but still doesn't know much more about the awesome commands of MS Excel which really excel your future in any organization where you need to handle data and store the data. Following are the some major commands which meant for data management and the use of these are simple too.
  1. IF , IFERROR, IFNA - These are logical test functions and used to what actions is to be performed under defined set of conditions.
  2. LOOKUP, VLOOKUP, HLOOKUP - These are called lookup functions and used to lookup / find some type of data with reference to data available in a set of array of data.
  3. INDEX - This function is also belong to lookup family but this function is used to index the value with reference of two availeble data.
  4. MATCH - This function also belong to same lookup family and used to get the position of the data in any single row or column.
  5. LEN, LEFT, RIGHT, MID - These function belongs to text function family and used to get the defined length lenght of a text string or find the total length of the text string written within the cell.
There are more commands also which can further be used to more simplify the work.
Now the question arieses........

From where you can learn these commands ?
Don't worry ! we are here to help you out.
Just visit www.excelwithease.com and learn MS excel in an easy way

HLOOKUP

HLOOKUP

No Comments

“HLOOKUP”

Used for – “HLOOKUP” function in MS Excel is used to looks up a value only in Top row of the selected table or array and returns a value in the same column from a row you specify. Here you should know that Excel considered the table by default as in ascending order from left to right.

Syntax – HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).
lookup_value – It is any text/number/formula/reference cell, you typed or calculated which needs to be find in a row or column. It is compulsory field without which formula results in #N/A error.
table_array – This is a vector or you can say a row or a column in which you want to search the value. It is compulsory field without which formula results in #N/A error.
row_index_num – This is a number of a row starting from top, from which you want to display the value as a result. It is also a compulsory field without which formula results in #VALUE! Error.
[range_lookup] – This is an optional field. It can be used to tell MS Excel that lookup value should be exactly the same or it will be approximate match the lookup value. Value in this position can be “TRUE”/1 for approximate match and “FALSE” / 0 / blank for exact match.

Example – Following example will clear the HLOOKUP function:-


A
B
C
D
E
F
G
H
I
1
Count
1
1.9
2.1
4
5
To search
2
1.9
2
Alphabet
A
B
C
D
E
Value in Col B approx. match
=VLOOKUP(H1,A1:C6,2)
=VLOOKUP(I1,A1:C6,2)
3
Location
Delhi
Mumbai
Calcutta
Bangalore
Gurgaon
Value in Col C approx. match
=VLOOKUP(H1,A1:C6,3,1)
=VLOOKUP(I1,A1:C6,3,1)
4






Value in Col B exact match
=VLOOKUP(H1,A1:C6,2,”FALSE”)
=VLOOKUP(I1,A1:C6,2,”FALSE”)
5






Value in Col C exact match
=VLOOKUP(H1,A1:C6,3,0)
=VLOOKUP(I1,A1:C6,3,0)
6









7










And result you get as under:-

A
B
C
D
E
F
G
H
I
1
Count
1
1.9
2.1
4
5
To search
2
1.9
2
Alphabet
A
B
C
D
E
Value in Col B approx. match
B
B
3
Location
Delhi
Mumbai
Calcutta
Bangalore
Gurgaon
Value in Col C approx. match
Mumbai
Mumbai
4






Value in Col B exact match
#N/A
B
5






Value in Col C exact match
#N/A
Mumbai
6









7










Note:- To get the right result with HLOOKUP you must remember following :-
1.     Top row must be in ascending order from left to right.
2.     It always works from left to right
3.     It stops at the first value that matches the lookup value in case there are duplicate values in the row.
4.     Works only if the lookup value is within the top row of the selected array.

5.     In case exact match you should have written “TRUE” or 0 for range_lookup.
VLOOKUP

VLOOKUP

No Comments

“VLOOKUP”

Used for – “VLOOKUP” function in MS Excel is used to looks up a value only in first column starting from the left of the selected table or array and returns a value in the same row from a column you specify. Here you should know that Excel considered the table by default as in ascending order.

Syntax – VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
lookup_value – It is any text/number/formula/reference cell, you typed or calculated which needs to be find in a row or column. It is compulsory field without which formula results in #N/A error.
table_array – This is a vector or you can say a row or a column in which you want to search the value. It is compulsory field without which formula results in #N/A error.
col_index_num – This is a number of a column starting from left, from which you want to display the value as a result. It is also a compulsory field without which formula results in #VALUE! Error.
[range_lookup] – This is an optional field. It can be used to tell MS Excel that lookup value should be exactly the same or it will be approximate match the lookup value. Value in this position can be “TRUE”/1 for approximate match and “FALSE” / 0 / blank for exact match.

Example – Following example will clear the VLOOKUP function:-

A
B
C
D
E
F
G
H
I
1
Count
Alphabet
location

To search
2
1.9


2
1
A
Delhi

Value in Col B approx. match
=VLOOKUP(F1,A1:C6,2)
=VLOOKUP(G1,A1:C6,2)


3
1.9
B
Mumbai

Value in Col C approx. match
=VLOOKUP(F1,A1:C6,3,1)
=VLOOKUP(G1,A1:C6,3,1)


4
2.1
C
Calcutta

Value in Col B exact match
=VLOOKUP(F1,A1:C6,2,”FALSE”)
=VLOOKUP(G1,A1:C6,2,”FALSE”)


5
4
D
Bangalore

Value in Col C exact match
=VLOOKUP(F1,A1:C6,3,0)
=VLOOKUP(G1,A1:C6,3,0)


6
5
E
Gurgaon






7










And result you get as under:-

A
B
C
D
E
F
G
H
I
1
Count
Alphabet
location

To search
2
1.9


2
1
A
Delhi

Value in Col B approx. match
B
B


3
1.9
B
Mumbai

Value in Col C approx. match
Mumbai
Mumbai


4
2.1
C
Calcutta

Value in Col B exact match
#N/A
B


5
4
D
Bangalore

Value in Col C exact match
#N/A
Mumbai


6
5
E
Gurgaon






7










Note:- To get the right result with VLOOKUP you must remember following :-
1.     First column must be in ascending order from top to bottom.
2.     It always works from top to bottom
3.     It stops at the first value that matches the lookup value in case there are duplicate values in the column.
4.     Works only if the lookup value is within the leftmost column of the selected array.
In case exact match you should have written “TRUE” or 0 for range_lookup.

Is this article helpful to you?

Amazon