LOOKUP for price ranges with multiple criteria

[carolineh]

Hi,
I need help with a LOOKUP formula with multiple criteria. I am using Excel 2010.
So basically i need to return a pre-determined price range bucket but the price range buckets are different for each Div/Dept.
Example of data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Division[/TD]
[TD]Department[/TD]
[TD]Price[/TD]
[TD]Price Bucket[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]mens private label[/TD]
[TD]accessories[/TD]
[TD]$49.00[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]men[/TD]
[TD]socks and underwear[/TD]
[TD]$16.99[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]men[/TD]
[TD]advanced designer[/TD]
[TD]$349.00[/TD]
[TD]??[/TD]
[/TR]
</tbody>[/TABLE]
row 1 price bucket should return $25-49
row 2 should return $15-19
row 3 should return $300-499

Table reference

[TABLE="width: 646"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Division[/TD]
[TD]Department[/TD]
[TD]Price[/TD]
[TD]Price bucket[/TD]
[/TR]
[TR]
[TD]mens private label[/TD]
[TD]accessories[/TD]
[TD]0[/TD]
[TD]$0-24[/TD]
[/TR]
[TR]
[TD]mens private label[/TD]
[TD]accessories[/TD]
[TD]25[/TD]
[TD]$25-49[/TD]
[/TR]
[TR]
[TD]mens private label[/TD]
[TD]accessories[/TD]
[TD]50[/TD]
[TD]$50-74[/TD]
[/TR]
[TR]
[TD]mens private label[/TD]
[TD]accessories[/TD]
[TD]75[/TD]
[TD]$75-99[/TD]
[/TR]
[TR]
[TD]mens private label[/TD]
[TD]accessories[/TD]
[TD]100[/TD]
[TD]$100-149[/TD]
[/TR]
[TR]
[TD]mens private label[/TD]
[TD]accessories[/TD]
[TD]150[/TD]
[TD]$150-199[/TD]
[/TR]
[TR]
[TD]mens private label[/TD]
[TD]accessories[/TD]
[TD]200[/TD]
[TD]$200-249[/TD]
[/TR]
[TR]
[TD]mens private label[/TD]
[TD]accessories[/TD]
[TD]250[/TD]
[TD]$250-299[/TD]
[/TR]
[TR]
[TD]mens private label[/TD]
[TD]accessories[/TD]
[TD]300[/TD]
[TD]$300-399[/TD]
[/TR]
[TR]
[TD]mens private label[/TD]
[TD]accessories[/TD]
[TD]400[/TD]
[TD]$400-499[/TD]
[/TR]
[TR]
[TD]mens private label[/TD]
[TD]accessories[/TD]
[TD]500[/TD]
[TD]$500-999[/TD]
[/TR]
[TR]
[TD]mens private label[/TD]
[TD]accessories[/TD]
[TD]1000[/TD]
[TD]$1000+[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]socks and underwear[/TD]
[TD]0[/TD]
[TD]$0-4[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]socks and underwear[/TD]
[TD]5[/TD]
[TD]$5-9[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]socks and underwear[/TD]
[TD]10[/TD]
[TD]$10-14[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]socks and underwear[/TD]
[TD]15[/TD]
[TD]$15-19[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]socks and underwear[/TD]
[TD]20[/TD]
[TD]$20-24[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]socks and underwear[/TD]
[TD]25[/TD]
[TD]$25-29[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]socks and underwear[/TD]
[TD]30[/TD]
[TD]$30-39[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]socks and underwear[/TD]
[TD]40[/TD]
[TD]$40-49[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]socks and underwear[/TD]
[TD]50[/TD]
[TD]$50-74[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]socks and underwear[/TD]
[TD]75[/TD]
[TD]$75-99[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]socks and underwear[/TD]
[TD]100[/TD]
[TD]$100+[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]advanced designer[/TD]
[TD]0[/TD]
[TD]$0-49[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]advanced designer[/TD]
[TD]50[/TD]
[TD]$50-99[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]advanced designer[/TD]
[TD]100[/TD]
[TD]$100-299[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]advanced designer[/TD]
[TD]300[/TD]
[TD]$300-499[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]advanced designer[/TD]
[TD]500[/TD]
[TD]$500-749[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]advanced designer[/TD]
[TD]750[/TD]
[TD]$750-999[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]advanced designer[/TD]
[TD]1000[/TD]
[TD]$1000-1499[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]advanced designer[/TD]
[TD]1500[/TD]
[TD]$1500-1999[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]advanced designer[/TD]
[TD]2000[/TD]
[TD]$2000+
[/TD]
[/TR]
</tbody>[/TABLE]


Thank you very much for your help! [TABLE="width: 395"]
<colgroup><col><col><col span="2"></colgroup>[/TABLE]

 

[Aladin Akyurek]

Upper exhibit...

In D2 control+shift+enter, not just enter, and copy down:

=LOOKUP(C2,IF(Sheet2!$A$2:$A$33=$A2,IF(Sheet2!$B$2:$B$33=$B2,Sheet2!$C$2:$C$33)),Sheet2!$D$2:$D$33)

The table the formula consults is in A:D of Sheet2.

 

[carolineh]

Thank you Aladin, it worked wonders!!

 

[carolineh]

Thank you Aladin, it worked wonders!

 

[Aladin Akyurek]

Thank you Aladin, it worked wonders!

You are welcome. Thanks for the update.