On 2002-03-09 09:36, sm2200 wrote:
I have experience using VLOOKUP in Lotus and Symphony. Now I want to use a VLOOKUP in Excel but I'm running into a problem. To isolate the problem I set up a very simple basic VLOOKUP array and VLOOKUP column and still am getting incorrect readings. It is a very very simple array and I can't figure out the problem. As I said I'm new to Excel.
I believe emailing the array to anyone interested in finding the problem would be the simplest (only) way to figure it out. Let me know your email address and I'll send it.
Thanks,
Sam Marx
Sam,
Consider the following sample
{1,2.3;2,2.4;3,8.7;5,2.1;6,3.5}
in A2:B6. That is, A2 houses 1, B2 2.3, A3 2, etc.
Suppose you want to retrieve the value associated with 4 from the above "table". The range A2:B6 is as it were a 2-column table.
Put this 4, which is a lookup value, in C2.
In D2 enter:
[1]
=VLOOKUP(C2,$A$2:$B$6,2)
In E2 enter:
[2]
=VLOOKUP(C2,$A$2:$B$6,2,0)
In [1], VLOOKUP executes an approximate match. That is, it looks in A2:A6 for a value that is (a) closest or (b)equal to 4.
This use of VLOOKUP requires that the "table" is sorted in ascending (or in some cases, in descending) order.
In [2], VLOOKUP is asked to execute an exact match between the lookup value in C2 and the values in A2:A6. The flag or match-type 0 signals the demand for an exact match. FALSE instead of 0 does the same. We could also have added 1 (or TRUE) in [1] to signal that we want a closest/approximate match. Omitting that boils down to the same.
The synntax of this function is thus:
VLOOKUP(lookup-value,lookup-table,find-column,match-type)
where match-type [0,1] or [FALSE,TRUE] is optional; find-column is specified by a number. In our lookup-table which is A2:B6, A2:A6 is column 1 and B2:B6 is column 2.
Hope this helps.
Aladin