"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.
*do download the attachment to see how it works. it is an excel attachment.
Alright, all the best.
