Vlookup function ?

[R Green - WoWsat.com]

This is my first time posting here... Even though I'm comfortable with
Excel, I believe there's still alot to learn regarding excel functions and
hence my question in this group...

I'm comfortable with certain functions such as nested IFs, AND, OR, Max,
Min, etc, but I have never been able to understand the array functions such
as VLookups...

The Excel examples are pitiful and since I'm not really a programmer have no
programming experience, perhaps someone here can provide me an example of an
array function? A simple example with the VLookup formula would be
great...it'd be great if you can email me a functional example of the excel
worksheet to (e-mail address removed) .. or if it can be illustrated here in this
group would be great also!

Thanks!

R Green

 

[Ken Wright]

It's easy to get lost in a formula if you don't really understand what the
formula is actually
doing:-

The VLOOKUP function essentially takes a value that you specify, whether it be a
hardcoded
number/letter/text or a value within a cell reference, and then goes and looks
it up in a table.
It will look for that value in the leftmost column of the table, and either find
it or the closest
match, and will then return the corresponding value on the same row, in whatever
column of that
table that you tell it to:-

Example - With the following table

D E
1 1 0.20
2 10 0.25
3 20 0.30
4 30 0.35
5 40 0.40

and with your value that you are looking up in say cell A1 (and let's assume it
is 25 for
example).

You can put a formula in pretty much any other cell, that says, take the value
in cell A1 (25), go
and look for it in the lefthand column of the table (D15), and then when you
have found it (or
the lowest closest number to it), go to the second column (or whichever one you
specify if there
are more than 2) and give me the number that it is on the same row in that
column.

So, with the formula being =VLOOKUP(A1,D1:E5,2) it will first take the value in
A1 which is 25,
then go look for it in the leftmost column (D) of your specified table (D1:E5),
and it will try to
find that number. Now it isn't there, so what it will do is look for the next
lower closest
number, which in this case will be 20, and the 2 in the formula says to go and
get the value in
the 2nd column (E) in your table, that is on the same row as the 20. That value
in this case is
0.3

If you put the value 30 or 31 or 32 etc into A1 now, you will see the result of
the formula
change, because now it will either find those numbers or the lower closest
number (and in each
case there it is 30), and will subsequently return 0.35 as the corresponding
value.

Just to show you how the 2 really works in that formula, if you added one more
column to your
table so that it looked like this:-

D E F
1 1 0.20 0.15
2 10 0.25 0.25
3 20 0.30 0.35
4 30 0.35 0.45
5 40 0.40 0.55

and you actually wanted the value from Col F, then you would simpl;y change the
2 in the formula
to a 3 to signify the third column, eg:-

=VLOOKUP(A1,D1:E5,3)

With the examples already given, 25 in A1 would return 0.35, and 30/31/32 would
return 0.45

The one caveat to all of this (When getting the nearest number is OK) is that
the data in your
leftmost column must be sorted in ascending order.


There are times when you would only want it to give you a value if you had an
exact match on the
number, and in this instance you would simply add a 4th argument of 0 or FALSE
to the formula,
eg:-

=VLOOKUP(A1,D1:E5,3,0)

or

=VLOOKUP(A1,D1:E5,3,FALSE)

In these cases you do not need to have the data in your leftmost column sorted.

 

[R Green - WoWsat.com]

Thank you Ken... I will work on this later on when I get home...

R Green

 

[Ken Wright]

My pleasure. Just post back if you need some elaboration on any part of it.
Once it clicks though, you'll love the simplicity of what it is actually doing.