Looking up a value based on a range

[jwalkerack]

Hey guys i was wondering if someone might be able to put me in the right direction for a following question

i think i need to do some kinda of VLOOK up , but i m not sure how to go about it.

i have two peices of informaiton the first is a table with sales peoples caterogries for sales. Like below

So for example , if sales person A , gets 500 sales in a month , it would be classed as type 1. The problem is that each person has a unique table which is unique. So example person B ranges and tpyes are different to person A and Person B


What i have on another Sheet is the list of the peoples names and there sales fiqures .. for the month. What i would like to have to be able to take the month sales fiqure and look this up and return the type that person , will get . Is this possible and if so how would i go about doing it and which funcation would i need to use.

Many Thanks Jack

Sales Person	bottom	Top	Type
A	0	1000	1
A	1001	2000	2
A	2001	3000	3
A	3001	4000	4
A	4001	5000	5
B	53	500	2
B	501	750	3
B	752	1300	4
B	1134	1650	6
c	100	2000	1
c	2001	5000	2
c	5001	6000	3
c	6001	6200	4
c	6201	7000	5

<colgroup><col style="width: 125pt; mso-width-source: userset; mso-width-alt: 6070;" width="166"> <col style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;" width="104"> <col style="width: 48pt;" width="64"> <col style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;" width="100"> <tbody>
</tbody>

 

[iggydarsa]

What you need is SUMPRODUCT (not VLOOKUP)

Lets say your search criteria is Person="A" and Sales="1002" then you should use the following formula:
=SUMPRODUCT(--(A2:A15="A"),--(B2:B15<=1002),--(C2:C15>=1002),D2:D15)

 

[jwalkerack]

Hi there thanks for you help . i have nearly 100 different people , with different sales figures tables. Using the Sumproduct , would it mean i would have to input each table into the forumala for each person. Is there another way around this ?

 

[Aladin Akyurek]

Sales Person	bottom	Top	Type
A	0	1000	1				a	2400	3
A	1001	2000	2				b	1000	4
A	2001	3000	3
A	3001	4000	4
A	4001	5000	5
B	53	500	2
B	501	750	3
B	752	1300	4
B	1134	1650	6
c	100	2000	1
c	2001	5000	2
c	5001	6000	3
c	6001	6200	4
c	6201	7000	5

<tbody>
</tbody>

J2, control+shift+enter, not just enter, and copy down:

=LOOKUP(I2,IF($A$2:$A$15=$H2,$B$2:$B$15),$D$2:$D$15)

 

[Skybluekid]

Have a look at this:https://www.dropbox.com/s/ct1r1kmfjh10v3p/Sumproduct.xlsx

 

[iggydarsa]

As long as you are using 4 columns, the rest doesnt matter.
Instead of "A" and 1002, you should use range as a variable tho.
Something like this:
=SUMPRODUCT(--(A2:A15=I2),--(B2:B15<=J2),--(C2:C15>=J2),D2:D15)