Skip to main content

Looking up for an example on VLOOKUP?

From all the questions I have been asked about and I have asked mostly are related to Excel Formulas Smile If I filter even further I will find that I am looking for information on vlookup most of the times…

Here is one attempt from my end to explain what vlookup is all about.

What’s VLOOKUP?

Ya ya apart from the obvious answer that its an excel formula, it’s a search function!

It can find you matching information from a particular table of data – something similar to a hashtable or dictionary in data structures Smile 

You give it an identifier and it will find the corresponding value for it…

 

Can I get a VLOOKUP Example?

Of course! That’s what this blog is about Winking smile 

Jokes apart, here is an example of VLOOKUP

Problem Statement: Your boss asks you to find out the work experience in number of years of your team mates

Data you have:

Pic1

Now you have to fill in this table to present it to your boss:

Pic2

Now you’ll get the data in columns H, I from C and D – isn’t it? that’s simple Smile 

You have a way to do that using VLOOKUP too – here’s how

All you need for VLOOKUP to work is a unique value to look for in a table and it would return the column value you ask for…

the formula for VLOOKUP is defined this way VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)

In this case,

unique value =  Lookup_value = Name

table you need to look at = Table_array = A2 to D7

return the column value you want = Col_index_num = C if you want “Company”, D if you want “Current Age”

Now the only optional value the formula needs is the “Range_lookup” – It can be either a true or false or blank

So follow these rules to fill in “Range_lookup”

  • If the column which has your unique identifier is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true , or leave it blank.
  • If the column which has your unique identifier is not sorted, or it’s sorted in descending order, then you must enter a value of false

 

You’re all set!

You need to fill in the following formulas to get the data:

For Company – =VLOOKUP(G2,A2:D7,3,FALSE)

For Current Age – =VLOOKUP(G2,A2:D7,4,FALSE)

For Experience in Years – =I2-VLOOKUP(G2,A2:D7,2)

 

Hope this helps!!!

Comments

Popular posts from this blog

wget error–“zsh: parse error near &”

There is no doubt that I prefer wget way over any other type of downloads… Syntax: wget <DOWNLOAD_URL>   If you get this error “ zsh: parse error near & ” then its probably because your download URL has a “&” so you should try giving your DOWNLOAD_URL in double quotes wget “<DOWNLOAD_URL>”   If you are trying to download from a site which needs you to give your credentials then you can try giving it this way wget --http-user=<UserName> --http-password=<Password> “<DOWNLOAD_URL>”   Hope this helps

How to Unpack a tar file on Windows?

On Windows: You can download a simple command line tool to do this. You can download the tool from here Usage can be found on the website but pasting it here too for convenience: C:\>TarTool.exe Usage : C:\>TarTool.exe sourceFile destinationDirectory C:\>TarTool.exe D:\sample.tar.gz ./ C:\>TarTool.exe sample.tgz temp C:\>TarTool.exe -x sample.tar temp TarTool 2.0 Beta supports bzip2 decompression for files with extensions like tar.bz2 and .bz2 . TarTool -xj sample.tar.bz2 temp or TarTool -j sample.bz2 Download TarTool 2.0 Beta from here Unpack a .txz file on Windows Use the 7zip tool  to unpack a .txz file on windows On Linux: You can use the bzip2 and tar combined to do this… for ex: bzip2 –cd <tar.bz_fileName> | tar –xvf - This will unpack the contents of the tar.bz file Happy Un-Tar-ing

Ubuntu: "Unlock login keyring" message "the password you use to log in your computer no longer matches that of your login keyring"

Resetting all the keyring passwords:  Navigate to  Places > Home . Press  ctrl   h  t o view "hidden files". Navigate to  .gnome2 > keyrings and delete   login.keyring Using the same keyring (resetting keyring password but keeping old passwords in keyring): 1) Make a backup of the keyring cd ~/.local/share/keyrings/ cp login.keyring login.keyring.backup and after that delete  login.keyring  file rm ~/.local/share/keyrings/login.keyring 2) Create a new keyring file from Gnome Keyring with the name "login" 3) Replace the new keyring file with the backup of the old keyring file cd ~/.local/share/keyrings/ mv login.keyring.backup login.keyring Note: before Ubuntu 12.10 the path to the keyrings folder was   ~/.gnome2/keyrings/   instead of  ~/.local/share/keyrings/