Thursday, 12 January 2012

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.


Another one,

In this example O41 (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).


raybanoutlet001 said...

los angeles clippers
cleveland cavaliers
ray ban sunglasses
chaussure louboutin
baltimore ravens jerseys
buffalo bills jerseys
cheap michael kors handbags
fitflops sale clearance
michael kors handbags
polo ralph lauren outlet

drones said...

Adoro i manifesti Pubblicitari in 3D per una Comunicazione Innovativa. Chiedi Info! Assistenza Tecnica · Qualità Professionale · Primi in Italia · Alta Velocità Tipi: Manifesti e Poster 3D, Locandine 3D, Espositori 3D, stampante 3d e Scenografie 3D, Render 3D. La tecnologia di stampa 3d permette modelli in pla e abs e molti altri materiali. Anche i droni li adoro, i droni professionali sono perfetti per le ispezioni e la termografia con drone. Best drones gimbal.