vlookups
 

hi,

Can someone point me to a good informational page please
explaining about vlookups on my excel sheet or have a
look through this!

Sometimes they work for me and sometimes they dont
sometimes I have headings on my rows should i or not?
does it matter where an array is on the sheet? What
about the source page? Headers or not? and then theres
the 1 that goes at the end of the vlookup after the
column number; true or false? whats that all about?

Rob

vlookups
 

Rob,

It is all explained in the Excel Help, under VLOOKUP.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
hi,

Can someone point me to a good informational page please
explaining about vlookups on my excel sheet or have a
look through this!

Sometimes they work for me and sometimes they dont
sometimes I have headings on my rows should i or not?
does it matter where an array is on the sheet? What
about the source page? Headers or not? and then theres
the 1 that goes at the end of the vlookup after the
column number; true or false? whats that all about?

Rob

vlookups
 

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(YourVLOO KUP))

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
HIS


"Rob" wrote in message
...
hi,

Can someone point me to a good informational page please
explaining about vlookups on my excel sheet or have a
look through this!

Sometimes they work for me and sometimes they dont
sometimes I have headings on my rows should i or not?
does it matter where an array is on the sheet? What
about the source page? Headers or not? and then theres
the 1 that goes at the end of the vlookup after the
column number; true or false? whats that all about?

Rob

vlookups
 

Debra Dalgleish has some nice instructions for =vlookup() at:
http://www.contextures.com/xlFunctions02.html

and you didn't ask, but she has some nice instructions for =index(match()) at:
http://www.contextures.com/xlFunctions03.html

Rob wrote:

hi,

Can someone point me to a good informational page please
explaining about vlookups on my excel sheet or have a
look through this!

Sometimes they work for me and sometimes they dont
sometimes I have headings on my rows should i or not?
does it matter where an array is on the sheet? What
about the source page? Headers or not? and then theres
the 1 that goes at the end of the vlookup after the
column number; true or false? whats that all about?

Rob

--

Dave Peterson

vlookups
 

You can find more information regarding the vlookup function over here
too:
'Excel vlookup'
(http://www.free-training-tutorial.co...n-vlookup.html)

Rob;4028462 Wrote:
hi,

Can someone point me to a good informational page please
explaining about vlookups on my excel sheet or have a
look through this!

Sometimes they work for me and sometimes they dont
sometimes I have headings on my rows should i or not?
does it matter where an array is on the sheet? What
about the source page? Headers or not? and then theres
the 1 that goes at the end of the vlookup after the
column number; true or false? whats that all about?

Rob


--
jackgopher