Rob
VLOOKUP looks up a value in an array of numbers. (A table) the value in the
table you are looking up must be in the far left column but can be on
another sheet or in another workbook.
Let's break it down
=VLOOKUP(A1,Sheet2!$A$1:$D$100,4,FALSE)
A1 - This is the cell value that will be 'looked up'. It could just have
easily been a constant (e.g "Nick") or a Range Name
Sheet2!$A$1:$D$100 - This is your 'lookup range'. (Notice it is an absolute
reference ($A$1, etc). This stops the range from 'moving' when you copy it
down to look up A2, A3, A4, etc. The value of A1 (The previous parameter),
can only be looked upo in the far left column (In this case A)
4 - This is the column 'Offset' in the row to return once (and if) the value
looked up is found. (In this case A is 1, B is 2, C is 3, and D is 4. So we
are returning the value in D corresponding to the row where the lookup is
found). It is a common mistake to enter $A$1:$A$100 as the second parameter
and then take 4 here. This will return a #REF! error as offset is not
contained within the lookup range. (You cannot look at the 4th column offset
in a 1-column range!)
FALSE - Looks up an exact match. If the value is not found you will get an
#N/A error (Not available), if you use TRUE here (or it will default to TRUE
if you have no parameter), you should sort the data in ascending order on
the lookup column and it will find the nearest match. In practice I find I
very seldom use TRUE and only when for example, you want the nearest
finishing time to an actual time.
That's about all that's needed on VLOOKUP.....
What you may need very soon in the future is to handle the #N/A error that
will inevitably happen with the FALSE parameter. To do this use the ISNA
error checking function
=IF(ISNA(VLOOKUP(YourVLOOKUP)),"",VLOOKUP(YourVLOOKUP))
This basically is saying if YourVLOOKUP causes an #N/A error then put a
blank ("") else do the VLOOKUP. This is very handy in data entry as the
formula will show a blank until someone enters something in the data entry
cell.
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
