Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IFs with VLOOKUPs | Excel Worksheet Functions | |||
Dependent vlookups - nested vlookups (maybe) | Excel Worksheet Functions | |||
VLOOKUPS | Excel Worksheet Functions | |||
Vlookups | Excel Discussion (Misc queries) | |||
Vlookups | Excel Programming |