Document Sample
VLOOKUP Powered By Docstoc
One of 3 types of lookup functions built into Excel. The aim is to enter the
VLOOKUP function into a cell – its job is to use a value in one cell and find that
value in a table. The table will be made up of two columns – one a list of values and
the second a corresponding set of values. For example, if someone was graded as
“grade a” salary – then a grade “a” person may get £12,000 per year. We want Excel
to find the “a” and return its corresponding value “£12,000”:

From the above, you will see I have set-up a spreadsheet to record employee names
and grades. There is then to the right a “lookup table”. The idea is that any salary
changes to be made will be done so in this table – and the results of the changes will
be reflected in the “salary” column. (It is easier to make one change in the lookup
table & have it automatically update related cells rather than having to go through
each employees salary & change it – especially if there are 1000+ employees)

The 3 main parts are lookup_value, table_array and Col_index_num. The
lookup_value is the actual value you want to look up in the lookup table: B2 in the
example above. As you go through the VLOOKUP wizard, just click on the cell or
type in B3. Then table_array: this is the entire lookup table – use the mouse to select
(shade) the cells F2 to G6 in the example above. The Col_index_num is a number –
the number of the column you want to return into the cell: 2 in the example above as
the second column contains the salary in £.

The Range_Lookup from the image above: if you want Excel to find the nearest
value then type TRUE or leave it blank. If you want to find an exact match enter
FALSE. An example of where that works is:
Now the lower example is a classic examination to grade lookup. The boundaries are
set in the lookup table. However, as there is little chance of students being exactly on
the boundary, the use of TRUE is relevant, as we need to find the nearest value. (Or
leave it blank as Excel assumes a blank entry is the same as “true”). FALSE may be
of use ONLY if you want to find an exact match. If you do need an exact match, just
type FALSE into the Range_Lookup field.