VlookUp in Excel

Learnt a lot about VlookUp today. It is really one of the most powerful features of MS Excel.

Few things to remember.
The formula will return a "0" (without quotes) if the index variable was matched but there as not corresponding value to return. If the index variable does not match, Excel will return #NA.

-remember the column number you want to retrieve. This column number is counted from the column where you "looked" for the index variable.

-You can lookup in another file.
-Linked files get updated even if they are not open.
-To keep track of what changes has been made it is a good idea to use the "TRACK CHANGES" feature.
-Excel cares about trailing spaces but does not care for difference in CASE and the type of the cell.
-Cells cannot be protected, hence you have to be very careful when you use vlookup.

Example of VLookup with data in 2 different sheets.

=VLOOKUP(A:A,Sheet3!A:B,2,FALSE)

Another one,
=VLOOKUP(O2,B2:K41,5)

In this example O2 (the first condition) is the address of the value that you want to MATCH, for example registration number, ssn etc. (the index) with  the source data file. You can also provide a value here, but giving a cell address makes it easier to copy a range. The second term, B2:K41 is the range of source data in which the first column includes your INDEX variable. The third value, 5 is the column number after the index. For example if your INDEX is in column B and your VALUE (ie name) is in column E, you should write 4 here). Make sure to remember that you need to select the complete range of columns for the lookup. For example, if you have school names in column A,  freq of particular symptom in column B and you want to get the total number of health room visits from columns E (while D has the corresponding school name), you need to select both column D and E for the second condition (not just one column), then put 1 for the index number.