Vlookup help please!

[niles]

I am trying to do a vlookup function. I don't understand what's wrong
with my formula. I am in an Excel class and the book doesn't explain
how to come up with the formula. Here's my info on the worksheet:

A15:B19 is a table with a range of sales and rate of commission.
Column A is the range of sales and column B is the rate of commission.


C4:C11 has the gross sales.

I am in D4 trying to come up with the employee's commission percent.

Here is my formula:
=VLOOKUP(D4,A15:B19,2)

What am I doing wrong in the formula? I suspect that it is something
with D4 in the formula.

Did this post make any sense? Thanks for your help.

 

[Andy Wiggins]

Have you looked in the Excel Help file (press F1)? It has some very good
examples that should help you. How? Reconstruct an example, from the Help
file, in your own workbook and then apply your class question to what you
have learnt.


--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[J.E. McGimpsey]

If you're looking up the individual commissions associated with each
sale, then you should use the value in C4 as your lookup value (for
the formula that goes in D4), and your formula should read:

=VLOOKUP(C4, A$15:B$19, 2)

You can then copy down from D4 to D11.

If instead you're trying to get the commissions for the total sales
in C4:C11, then you can enter in D4 only:

=VLOOKUP(SUM(C4:C11), A$15:$B19, 2)

 

[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.

 

[Don Guillett]

Have a look in HELP index for LOOKUP

 

[niles]

column A col. B col. C col. D
Employee Base Salary Sales
ZELINKO, MARY $10,000 $80,500
DORN, WILLIAM $10,000 $54,000
WALSH, MARGARET $13,000 $79,200
EVENSON, CHUCK $10,000 $36,200
SANDERS, CINDY $12,000 $25,000
GOODMAN, JACOB $14,000 $83,200
INMAN, JOHN $10,000 $50,000
MANLEY, EUGENE $10,000 $41,000

COMISSION IS CALCULATED AS FOLLOWS:
RANGE OF SALES COMM. RATE
0 - 24999 7.0%
25000 - 39999 15.0%
40000 - 54999 18.0%
55000 - 79999 20.0%
80000 AND UP 22.5%

There's my info that I'm working with. The information actually starts
in row 4. A4:C11 is the employee's name and salary. The table is in
A15:B19.

I've tried looking in the Excel help and couldn't figure it out. It
looked like what I was doing was right.

J.E. McGimpsey, I tried using the formula that you suggested, but
Excel returned #N/A.

I'm getting frustrated with this! Any ideas on what I'm doing wrong?

 

[Peo Sjoblom]

I believe J.E. McGimpsey has something about commissions here, just adapt
your numbers

http://www.mcgimpsey.com/excel/taxvariablerate.html

adapted to your data it might look like

=SUMPRODUCT(--(A1>{0;24999;39999;54999;79999}),(A1-{0;24999;39999;54999;7999
9}),{0.07;0.08;0.03;0.02;0.025})

with the sales in A1

 

[StarTrek an beyond]

If you want to have someone else look at your file, send it to
(e-mail address removed)
and I will see what I can do for you.

Wayne B

 

[Ken Wright]

Cells A15:A19 are 0, 25000, 40000, 55000, 80000 respectively (The beginning of each of your
ranges)
Cells B15:B19 are 7%, 15%, 18%, 20%, 22.5% respectively (Your percentages)

The lookup values you will be using I'm assuming are the ones in Col D (Not sure of the relevance
of the salary data in Col B, so it may be that you haven't told us something, or that you have and
I just missed it)

If the first sales amount is in C4, then the first vlookup fomula is going into D4, the second in
D5 and so on.

Going back to the first reply I gave you, you should, to be honest, with the data listed as above,
now be able to walk through the question pretty simply, but, we've had a hell of a time with the
groups, and I'm just happy to be able to get in and post something again, so what the hell. Plese
do us the courtesy though of actually walking through the steps and trying to understand what is
actually going on, rather than just take the answer - It may well help you in further questions
you get, as it is a very useful tool.

In D4 then:-
You want to take the value in C4 (Sales amount), look it up in the leftmost column of your
commission table, and then return whatever the appropriate commission percentage is from the row
it matches your sales data to, which in this case will from the second column.

=VLOOKUP(C4,$A$15:$B$19,2)

So, the C4 above represents the sales value being looked up
The $A$15:$B$19 represents your commission table (I'll explain the $s in a minute)
The 2 represents the fact that you want data from the second column (Col B) of the table

What you really want though, is to be able to simply copy the formula down to the other cells, so
that you don't have to retype it. If you did that without having the $ signs, the formula
references will all adjust accordingly, and the reference to the table will be wrong, eg:-

If you had just put this in D4, and then copied it to D5

=VLOOKUP(C4,A15:B19,2)

you would end up with

=VLOOKUP(C5,A16:B20,2)

which as you can see, has the wrong table references because they have all moved down by 1. The
lookup value has changed to C5 instead of C4 as you would want it to, but you need to lock that
table down to the orginal values. This is where the $ signs come in, because they will fix
whatever part of the formula you put them in front of. By putting $ signs in, as per my example
(Note that I didn't put them on the lookup value reference), then copying down to say D5 will give
you the following:-

=VLOOKUP(C5,$A$15:$B$19,2)

Copying to D611 as well give you:-

=VLOOKUP(C6,$A$15:$B$19,2)
=VLOOKUP(C7,$A$15:$B$19,2)
=VLOOKUP(C8,$A$15:$B$19,2)
=VLOOKUP(C9,$A$15:$B$19,2)
=VLOOKUP(C10,$A$15:$B$19,2)
=VLOOKUP(C11,$A$15:$B$19,2)

Note the pattern:- Only the lookup value reference is changing.

As my first note explained, each formula will take the lookup value, search for it in the first
(leftmost) column of your table, and if it can't find it, then it will look for the next smaller
value that does exist in the table, match it to that and then return the % from the second column
(Hence the 2 in the formula).

Hope this helps.