How to use index match to find (better than vlookup)

Okay, today your boss came to you and say:

"Hey, I want know how many stocks in hand we have for these 5 fruits: Apple, Banana, Cherry, Date & Durian and fill in this list for me will you?"

And you have like 80s type of fruits in hand. Yea, you can go through the list and pick up the number coz it is only 5 types of fruit right? What if the list given is 20,30? Yea you still can go through one by one if you want while using ctrl+F and typing the fruit type thinking that is the best way to do it then good, you are one of the best employee there is.

However, if you are LAZY and want to get it done fast and accurate regardless of how many type of fruits you have, this is how: Index Match.

=Index('X',match('B','Y',0)

X= what are you looking for? in this case it would how many stocks you have in hand?
B= Criteria/word that you are looking for, in this case it would be list that your boss given you
Y= Master list, the whole column.

Provided that:

Master list= Sheet named 'Fruits master data', Column B=list of fruits, Column C=stock in hand

Boss request=Sheet named 'Boss Request', Column B=Boss request, Column C=how many we have.

And the boss request starts with apple at cell B3, enter this formula in C3.

=INDEX('Fruits Master Data'!C:C,MATCH('Boss Request'!B:B,'Fruits Master Data'!B:B,0))

Drag down as long as the request goes and you can try and prove it with your infamous ctrl+F.

Business Application:

This can be used to do a reconciliation between 2 lists and also to transfer data from one list to another and also looking for data.

Index match is case sensitive and it is more flexible compared to vlookup.

Here's the example, study it at your own leisure:

https://docs.google.com/file/d/0B22og94RNnAKaEZHMGpWQ2hUbk0/edit?usp=sharing
*do download the attachment to see how it works. it is an excel attachment.

Alright, all the best.

How to join two text in separate cells

Hi! Today's topic is rather light and easy. I am going to show you the use of  '&' symbol. If you are not sure where to find this symbol, you can easily gets it by pressing shift+7.

Well, now you already know how to get the '&' symbol. I am going to show you the use of this symbol.

See, easy to understand right? However, the sentence does not make sense unless you put spaces between the words right? By adding this " " you can put spaces between those words. Here's how:

There is also a formula to this which is called concatenate, =concatenate( ). Yeah as straightforward as it sounds, it is easy to use.

How useless it might seem, don't ever underestimate the use of '&'. It is very useful when it comes to reconciliation which need two criteria to match because of the first criteria has the same figure/text/whatsoever with another set of data.

For example, recurring payments in your bank reconciliation, if you are doing reconciliation for few months you might end up with few payments, apart from the rest of the payment which has the same amount but different date.

So & can be used with the amount & date to match against what you have in your system.

Anyway, Happy trying. :)

How to make DD.MM.YYYY date format readable by Excel

My friend asked me before. How to make 23.05.2013 to 23 May 2013 and readable as date by Excel.
Some of you might say it is easy. Right click> Format Cell>change date format but in reality that is impossible as excel only reads 05/23/2013 format.

Now you will say, just rewrite the date to 05/23/2013. Yes you are right if, we are talking about 1 or 2 dates to work with. But let say we have like 100, 1000 dates. Rewriting the date is definitely not practical.

What you can do is formularize the next cell for it to re-arrange the format from DD.MM.YYYY to MM/DD/YYYY.

 Formularize each column B,C,D to extract Month, Day and Year:

Result:

Okay, now to consolidate the back the Month, Day and Year to form a Date.


Consolidate Formula:

Result in Column E:

Then you might think, pretty tedious just to convert a date? There is an easier way which you can consolidate everything in 1 formula

Consolidate in 1 long formula:

Result in Column F: 

Now congratulate yourself as you have a Date! Now to convert it do 23 May 2013 format.(To bad you still cannot use the Right click> Format Cell>change date format way. You can try but I assure you, you will not succeed).


 Use 'text' formula to change the format of the date:

How to color few cells with the same color without clicking the fill color button

Hi! Okay today I am going to reveal the fastest way to color cells with the same color without click the fill color button and the cells back and forth.

Okay now. Let say you want to color all of number 1 to yellow.



You can start by coloring the first number 1 as usual.

For the rest of number one, instead of clicking back and forth the cell and the fill color button, you can just activate the cell that you want the color to be changed to yellow and effortlessly press F4.

Easy! :). Actually F4 is a shortcut key to copy last action made by you. Eg: If let say the last action was deleting Row. You can easily delete another rows by selecting the row and pressing F4. Without having to right click and select delete row.

Have fun trying. :)



How to sum series of number with #n/a

Hi. Today I am gonna show you how to add accross numbers that has #n/a in it.

When you are using =sum formula for this series of number, it will definitely will return #n/a value.

Solving this is simple, you just have to use =sumif formula.

This formula means, we are summing up from C2 to C9 for whatever amount is not equal to #n/a.

In excel this sign '<>' means not equal to.

You can also apply this to sum only +ve amount or only -ve amount or whatever amount which is bigger than X as shown in below example which we are summing up amount more than 7.

Okay, that all for today then. Happy trying. :)