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

[TABLE="width: 326"]
<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>[TR]
[TD="width: 166, bgcolor: transparent"]Sales Person [/TD]
[TD="width: 104, bgcolor: transparent"]bottom [/TD]
[TD="width: 64, bgcolor: transparent"]Top [/TD]
[TD="width: 100, bgcolor: transparent"]Type [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1000[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent, align: right"]1001[/TD]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent, align: right"]2001[/TD]
[TD="bgcolor: transparent, align: right"]3000[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent, align: right"]3001[/TD]
[TD="bgcolor: transparent, align: right"]4000[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent, align: right"]4001[/TD]
[TD="bgcolor: transparent, align: right"]5000[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]53[/TD]
[TD="bgcolor: transparent, align: right"]500[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]501[/TD]
[TD="bgcolor: transparent, align: right"]750[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]752[/TD]
[TD="bgcolor: transparent, align: right"]1300[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]1134[/TD]
[TD="bgcolor: transparent, align: right"]1650[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c[/TD]
[TD="bgcolor: transparent, align: right"]100[/TD]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c[/TD]
[TD="bgcolor: transparent, align: right"]2001[/TD]
[TD="bgcolor: transparent, align: right"]5000[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c[/TD]
[TD="bgcolor: transparent, align: right"]5001[/TD]
[TD="bgcolor: transparent, align: right"]6000[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c[/TD]
[TD="bgcolor: transparent, align: right"]6001[/TD]
[TD="bgcolor: transparent, align: right"]6200[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c[/TD]
[TD="bgcolor: transparent, align: right"]6201[/TD]
[TD="bgcolor: transparent, align: right"]7000[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Sales Person[/TD]
[TD="width: 64, bgcolor: transparent"]bottom[/TD]
[TD="width: 64, bgcolor: transparent"]Top[/TD]
[TD="width: 64, bgcolor: transparent"]Type[/TD]
[TD="width: 40"][/TD]
[TD="width: 42"][/TD]
[TD="width: 36"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]a[/TD]
[TD="align: right"]2400[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]1001[/TD]
[TD="width: 64, bgcolor: transparent"]2000[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]2001[/TD]
[TD="width: 64, bgcolor: transparent"]3000[/TD]
[TD="width: 64, bgcolor: transparent"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]3001[/TD]
[TD="width: 64, bgcolor: transparent"]4000[/TD]
[TD="width: 64, bgcolor: transparent"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]A
[/TD]
[TD="width: 64, bgcolor: transparent"]4001[/TD]
[TD="width: 64, bgcolor: transparent"]5000[/TD]
[TD="width: 64, bgcolor: transparent"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]53[/TD]
[TD="width: 64, bgcolor: transparent"]500[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]501[/TD]
[TD="width: 64, bgcolor: transparent"]750[/TD]
[TD="width: 64, bgcolor: transparent"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]752[/TD]
[TD="width: 64, bgcolor: transparent"]1300[/TD]
[TD="width: 64, bgcolor: transparent"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]1134[/TD]
[TD="width: 64, bgcolor: transparent"]1650[/TD]
[TD="width: 64, bgcolor: transparent"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]c[/TD]
[TD="width: 64, bgcolor: transparent"]100[/TD]
[TD="width: 64, bgcolor: transparent"]2000[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]c[/TD]
[TD="width: 64, bgcolor: transparent"]2001[/TD]
[TD="width: 64, bgcolor: transparent"]5000[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]c[/TD]
[TD="width: 64, bgcolor: transparent"]5001[/TD]
[TD="width: 64, bgcolor: transparent"]6000[/TD]
[TD="width: 64, bgcolor: transparent"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]c[/TD]
[TD="width: 64, bgcolor: transparent"]6001[/TD]
[TD="width: 64, bgcolor: transparent"]6200[/TD]
[TD="width: 64, bgcolor: transparent"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]c[/TD]
[TD="width: 64, bgcolor: transparent"]6201[/TD]
[TD="width: 64, bgcolor: transparent"]7000[/TD]
[TD="width: 64, bgcolor: transparent"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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)