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 |
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)
Below are the usual errors, encountered mostly by the beginners
A Must, to avoid some errors:
Example:
 |
| Vlookup sample error 1 |
Make sure you write the correct 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”
Excel Crack 