Full Guide: The Logic Behind Vlookup Function Excel


Tired of looking up or finding a value within a set of range using MS Excel? Non-stop of pressing CTRL F (shorcut of searching tools) just to find your value? Why not try some other options? There are several options that can solve your problems.

These are:

The Vlookup, Hlookup, and the combination of Index and Match function.

We shall discuss first, the Vlookup Function, but for now we need to know the concept of what is Vlookup and how Vlookup differs from the other referencing functions? Vlookup, it means Vertical – Column. It will help you find the value (your value, eg. name) in a given set of data range by row, while Hlookup is for horizontal, meaning by column and the “Index Match” function is perfect for finding a value in a set of range but it is not easy to configure.

But why Vertical is by rows? and Horizontal is by columns? For Vlookup means Vertical Lookup from the word itself Vertical (from top cell to bottom cell of each rows)

eg. Cell A1, A2, A3, A4, …

Finding your value by Vertical Line and by its Row

and for Hlookup, just the opposite of Vlookup

The Formula of Vlookup:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Still confused?

Let’s begin with a scenario:

User: Computer, go find this person from that location, search every room and if you found him, give me his Identification Number (Id no.) now!
Computer: Affirmative!

Take a look at this image (you can click it to enlarge)

Vlookup function cycle
Vlookup function cycle

lookup_value = Robert (H7)
table_array = Name and Id no. (D6:E13)
col_index_num = 2
range_lookup = false

False means Exact Match
True means Approximate Match

=VLOOKUP(H7,D6:E13,2,FALSE)

That’s it, you can now find your value in an easy way, say goodbye to “control f copy-paste function” 🙂

Below are the usual errors, encountered mostly by the beginners

A Must, to avoid some errors:

 
Always look the “lookup_value” from the 1st column of the table array
Make sure you write the correct col_index_num (starting from the 1st column of the table array)
When there are duplicate Values in your 1st column of the table array, the first value that matches the “lookup_value” is the outcome value of the col_index_num.

Example:

 
Always look the “lookup_value” from the 1st column of the table array
 
Vlookup sample error 1 improper selection of table array
Vlookup sample error 1

Make sure you write the correct col_index_num

vlookup sample error 2, incorrect inputting of the col index num
Vlookup sample error 2




When there are duplicate Values in your 1st column of the table array

Sample 1
Sample 2

These are the usual errors that experienced by many users mostly beginners. You can also use the “True” for the “range_lookup” but it is not accurate, for safety use I recommend “False” for “range_lookup” which is “exactly match”



Don’t forget to subscribe or follow me on google + for more future posts


Hit this post a PLUS! 🙂

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s