How to use Vlookup in Excel

Vlookup is one of the most useful Excel functions, yet one of the least understood.  The V here stands for vertical and lookup stands for the utility of the function.

Vlookup allows you to look for values in a table through columns (for rows Hlookup can be used, but, since most of our data is in Column form Vlookup will be the more frequently used of the two).

Lets go through a live example of Vlookup to understand it's use and how to run it.

Lets us consider table A, where marks in three subjects of some students are listed.

Now we have to find out marks of ' some ' of these students in table B shown below:

applying Vlookup

lookup_value : The value in Table B that has to be searched for in table A (select C in table B)

Table array: The table in which the value has to be searched for.Select the whole table A

col_index_num: The column number in the selected  table A from where the values will be picked up. Here it is two for now as we are looking for maths numbers.

range_lookup: Here we are given two choices True for approximate matches and false for exact matches. I have selected for exact matches in the above example

This is the result. Now we can just drag the formula to get results for the other fields.

I hope this article helps.

How to count rows/cells after the table is filtered:Excel

Suppose a table has x rows with values. We filter it for certain query and count the elements in  now filtered output. It still returns as the count. How do we count rows/cell in a filtered data?

Let us go through this example:

The COUNTA function returns the value to be 20.

Now if we I filter it for people with only number 1 .

The count is still 20.

Now to count for how many people have  number A we will have to use the subtotal function.

funtion_num is the function we want to use on the 'sub-data' that is the filtered table. Here we will select 3 i.e. COUNTA.

ref: select the data to be counted

So we get our subtotal to be 11.

Picking up a few characters from cell:Excel

To pickup a certain few characters from a data entry in  a cell we have three different functions.

1. MID function : This function has three arguments to be entered. The cell from which the characters/string will be taken, the position of the string from where we want to start and the number of strings to be taken.

2. Left function: Only two arguments to be entered .The cell from which the characters/string will be taken and the number of characters be taken.

3. Right function: Starts from the right side. Syntax same as left.

Below is example of usage of all the three. with the functions and the results listed for each.

The DARK KNIGHT'S Silent Dusk

Rahul's last match was coincidentally  Sachin's last match in 'colored' clothing too.The way Rahul Sharad Dravid has retired sums up the story of his entire career. Completely overshadowed..!!! 
Many found his batting boring and slow, many find it reassuring, but, without doubt he was the dam that saved Indian from many catastrophes. For me arguing between Rahul and Sachin is like debating who is better amongst the Batman and the superman. Comparing these two is an insult to either, but, each one had his own role. But, there's something in the Batman which makes me respect and connect to him more. While one was born to be a Hero the other fought his weaknesses to be compared. It requires a superhuman effort to raise yourself to a level where you are compared to a kryptonian living amongst lesser mortals and still not considered any less.

But, overshadowed, yes he was. Here are a few such instance I could remember and google.

Starting from the start, he scored 96 at Lord’s on his debut, In the same match debutant Ganguly scored 131 in the same match.
You remember the match where Sachin got a century the day after his father demised against Kenya in '99 WC, right? Did you know, even Dravid scored a century in that match?
Also he was the top run getter in 1999 worldcup. But we didnt do well in that worldcup (Didn't even reach the semis). Only few people remember Dravid’s 461 runs.
A score of 153 against New-Zealand  forgotten because Tendulkar hit 186 that match.
He scored 145 in the 1999 worldcup in England; in the same match Ganguly scored 183.
Remember that legendary match against the Australians in 2001? The one where Laxman scored 281 and Bhajji hit a hat-trick He scored 180 in 2nd innings too.
His 50 of 22 balls is the second fastest fifty by an Indian in ODI. A very unlikely Dravid innings. But in the same match Sachin and Sehwag scored hundred. How many people remember this innings? ( NewZealand, 2003)
He also has the second highest score by an Indian outside India to his name (270 at Rawalpindi, 2004). The highest score by an Indian outside India was hit by Sehwag (309) in the same series at Multan.

Indian team also holds the record for most consecutive wins while chasing, 17 wins.Though Dravid is adjudged an Unsuccessful captain for 2007 worldcup, he was a captaining the team in 15 of those matches.

He has stood in slips and retired with a un-matched figure of 210 test catches. Never shy of standing at short leg on a chilly morning.He was asked to keep wickets in ODIs, he did that too. He was asked to open at Lahore, he gave a 410 run opening partnership with Sehwag. He was thought to be  too slow for ODIs, he responded with fastest 50 by an Indian and two 300+ partnerships in ODIs. He was told that he was not a twenty-20 player, he responded by hitting 3 sixes in 3 balls. 

You know, everyone worships the God but you cannot become one and that’s the case with Sachin, you can only worship him,yet, cannot be him. But Dravid, he stood there and showed that through hard work and dedication a common man could make his way to greatness by conquering his imperfections. He stood for the symbol which Batman fans term as “Batman can be anyone.” He stood there as a inspiration that a human can stand shoulder to shoulder to the Gods by sheer strength of will, and on a given day...... better him.

Navjot Singh Sidhu had once said “Rahul Dravid is a player who would walk on broken glass if his team asks him to.” He was that kind of player.
And now, my heart breaks for the fact that even in his last match, he was eclipsed by Tendulkar who was playing his last match in ‘colored’ clothing.
 
Dravid was the hero we needed, while Tendulkar was the hero we always.....wanted. So, like Batman he chose the darkness of Shadows his Teammates cast over him, to work for the larger cause.
So I guess, the nation will applause Tendulkar not him in his last match. Because he can take it. But, he will be, for me, the silent guardian the watchful protector of the Indian Cricket Team.