Pulling Values within a Range

[Thomas_85]

Hi guys,

I was hopeful someone could help me with a formula that will look up a number (E3) and return an associated profile that is within a certain range (range of 100 bins).... For example I know that on Week 2 I will process 1200 bins (F3), and I know that the Heavy (1) profile should be used. I'm not sure if I should use a vlookup or an index/match, or if that's even the right way to go about it.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Profile
[/TD]
[TD] Bins
[/TD]
[TD] Cost
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]Week 1
[/TD]
[TD]Week 2
[/TD]
[TD]Week 3
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Skeleton (1)
[/TD]
[TD]520
[/TD]
[TD]$20K
[/TD]
[TD][/TD]
[TD]Bins:
[/TD]
[TD]500
[/TD]
[TD]1200
[/TD]
[TD]1100
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Skeleton (1A)
[/TD]
[TD]600
[/TD]
[TD]$45K
[/TD]
[TD][/TD]
[TD]Profile Used:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Skeleton 2
[/TD]
[TD]720
[/TD]
[TD]$60K
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Heavy (1)
[/TD]
[TD]1000
[/TD]
[TD]$75K
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Heavy (1A)
[/TD]
[TD]1500
[/TD]
[TD]$100K
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks a ton!

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Profile​

[/td][td="bgcolor:#F3F3F3"]

Bins​

[/td][td="bgcolor:#F3F3F3"]

Cost​

[/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]

Week 1​

[/td][td="bgcolor:#F3F3F3"]

Week 2​

[/td][td="bgcolor:#F3F3F3"]

Week 3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Skeleton (1)[/td][td]

0​

[/td][td]$20K[/td][td][/td][td]Bins:[/td][td]

500​

[/td][td]

1200​

[/td][td]

1100​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Skeleton (1A)[/td][td]

600​

[/td][td]$45K[/td][td][/td][td]Profile Used:[/td][td="bgcolor:#E5E5E5"]Skeleton (1)[/td][td="bgcolor:#E5E5E5"]Heavy (1)[/td][td="bgcolor:#E5E5E5"]Heavy (1)[/td][td]F3: =INDEX($A$2:$A$6, MATCH(F2, $B$2:$B$6))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Skeleton 2[/td][td]

720​

[/td][td]$60K[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]Heavy (1)[/td][td]

1000​

[/td][td]$75K[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]Heavy (1A)[/td][td]

1500​

[/td][td]$100K[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]