Multiple Vlookup Excel Question?

Question

Ok so here is my scenario

I have a table that looks like whats below, we are trying to derive what to pay an individual per unit based on 3 different variables.

-His Customer service score (Above region, or below)
-His Sold Unit Count (0-6,7-10,11-12,13+)
-Longevity Bonus (0-1 year,1-2 years,2+ years)

We'll Pay $150,$200,$250 or $350 as seen below in the table
So I have a table set up that looks like the following

(U.C) stands for Unit Count

So my question is, should I try and use multipe vlookups? a bunch of If statements? or where should i go, basically I am trying to have a cell that says, For every unit I sell, my payline (which is cell A2) is _____

0-1 Year 1-2 Years 2+ Years
(U.C) CSI-Y CSI-N CSI-Y CSI-N CSI-Y CSI-N
0.0 150 150 250 200 350 250
1.0 150 150 250 200 350 250
2.0 150 150 250 200 350 250
3.0 150 150 250 200 350 250
4.0 150 150 250 200 350 250
5.0 150 150 250 200 350 250
6.0 150 150 250 200 350 250
7.0 200 150 250 200 350 250
8.0 200 150 250 200 350 250
9.0 200 150 250 200 350 250
10.0 200 150 250 200 350 250
11.0 250 200 250 250 350 250
12.0 250 200 250 250 350 250
13.0 350 250 350 250 350 250
14.0 350 250 350 250 350 250


Thanks again in advance! You guys are very helpful

Answer 1

hmm, that's an interesting one. If I read this correctly you basically have 3 levels. Level 1 is based on length of time. Level 2 is based on qty (UC) and Level 3 is based on CSI-Y or CSI-N. So if you're at level 1 (0-1 years) and your UC is 10 and your rated as CSI-Y, you'd get 200. But if you were rated as CSI-N you'd only get 150.



based on that assumption the way I would do this is first

create a separate list for each of the year levels.



so you'll have

Level 1 (0-1 Years)

UC CSI-Y CSI-N

0 150 150

1 150 150

etc



then create another list of

Level 2 (1-2 Years)

UC CSI-Y CSI-N

0 150 150

1 150 150

etc



and a 3rd level for 2+ years.



now name each range (Insert > Name > Define. For example let's assume level 1 is in A2 thru C16 (A1 has the column headings). Define/Name that range "Level1" and assign it to be A2:C16

do the same for the range Level2 and for Level3



now you're going to have to have 3 cells which will hold your 3 criteria

say A18 will be your UC level, A19 will be your CSI-N or CSI-Y criteria and A20 will be your Level criteria



so in A18 enter 10

in A19 enter "CSI-Y"

in A20 enter "Level1" (or whatever your named range is)



now in A21 you'll have to create an IF and mulitiple VLOOKUP formula of



=IF(A19="CSI-Y",VLOOKUP(A18,INDIRECT (A20),2,FALSE), VLOOKUP(A18,INDIRECT(A20), 3,FALSE))



this will look at A19 and if it's CSI-Y we'll to do a vlookup on the UC and get the info from the 2nd column. If it's CSI-N, we get the info fromt the 3rd column. The INDIRECT(A20) will evaluate to INDIRECT(Level1). This formula tells excel to use the named range "Level1" which we've defined to be A2:C16. If cell A20 was "Level2", it would lookup the range that we defined as Level2, etc.



one thing your sheet doesn't really factor in though is what if someone sells 15 units? If the payment would continue to increase as the UC went up, you'd just have to keep expanding the tables (and redefine your named ranges). If the payment is maxed out at 14 so that even if I sold 100 units, I'd only get the 14 payment level, you could just call it "14+" and then in your criteria cell (A18) enter "14+" instead of 14.



i know the above is probably confusing as heck...but it does work :-))

Answer 2

I found a small macro works better than the mass confusion I see in juggling multiple variables such as your case.



Here's a link to the file.



http://www.savefile.com/files/1751469



I realize I took liberties in creating a table in a certain location, and your variables along the first row, but if you're familiar with macros, you can easily change this in the code.



If you further assistance, feel free to contact me via Y-Answers (double click my avatar)

Answer 3

documents in cols A to D J1 is company to seek col A for In cellular E2 enter =IF($J$a million=A2, ROW(),"") reproduction down documents list F2 =IF(ROWS($a million:a million)>count number($E:$E), "",INDEX(A:A, tournament(SMALL($E:$E, ROWS($a million:a million)), $E:$E,0)) reproduction in the course of three columns then reproduction all 4 cells down required kind of lines (say 10, do not mission lines without documents will teach as blanks)