|
||||||||||||||||||||
|
|||||||||||||||||||||
|
|||||||
| Notices |
 |
|
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#2
08-06-2007, 06:54 AM
|
||||
|
||||
|
Excel help is a good place to start, otherwise, here's an example
=VLOOKUP(A2,D1:F10,2,0) This formula looks up A2 in the first column of the range (D1:D10) and, if found, returns the value in the same row from the column specified. The 2 dictates column 2 of the range, i.e. column E. ...so if A2 is found first in D5 then the formula returns the value in E5 This is the most common version of VLOOKUP, with a 4th argument of 0 (or FALSE) which means that VLOOKUP searches for an exact match only and nothing needs to be sorted. If you use VLOOKUP with no 4th argument (or use 1 or TRUE), e.g. =VLOOKUP(A2,D1:F10,2,1) then D1:D10 (the "lookup range") must be sorted ascending and the formula will return the value from E1:E10 on the same row as the "closest match" in D1:D10, i.e. the greatest value smaller than or equal to the lookup value (A2) LOOKUP always works like this second version of VLOOKUP, i.e lookup range must be sorted ascending. You can either use the "vector" type, =LOOKUP(A2,D1:D10,E1:E10) where match is found in first vector (D1:D10) and value returned from corresponding position of the second (E1:E10), this is especially useful for "left lookups" where return column is to the left, e.g. =LOOKUP(A2,D1:D10,C1:C10) or the "range" type where lookup range is the first column of the lookup range and return range is the last - except where lookup range is wider than it is tall, in which case the lookup range is the first row and the return range is the last row, e.g. =LOOKUP(A2,D1:F10) looks up A2 in D1:D10 and returns the corresponding value form F1:F10. =LOOKUP(A2,D1:H3) looks up A2 in D1:H1 and returns the corresponding value form D3:H3. This last version is similar to HLOOKUP 
|
|
| Bookmarks |
New topics in F1 Get the most out of Excel Formulas & Functions
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Linear Mode
