VLOOKUP based on multiple criteria, one is age range

[lemony]

Hey,

I have a bit of a problem, I need a formula to look something up based on two variables.

So the info that will be input is Name, Group and Age, eg.

Name: John
Group: Blue
Age: 40
Member Price:

I need it to draw the member price based on the group and age;

[TABLE="width: 500"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Age Range[/TD]
[TD]Member $[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]10-14[/TD]
[TD]$16,000[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]15-16[/TD]
[TD]$17,000[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]17-18[/TD]
[TD]$18,000[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]19-24[/TD]
[TD]$20,000[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]25-29[/TD]
[TD]$24,000[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]30-32[/TD]
[TD]$26,000[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]33-34[/TD]
[TD]$32,000[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]35+[/TD]
[TD]$36,000[/TD]
[/TR]
</tbody>[/TABLE]

I've organized the table in my spreadsheet so that it only lists the youngest age, and I've tried using INDEX and MATCH with IF, and SUMPRODUCT (which I know won't work unless I list every age from 1-99), and nothing works...I'm really stuck on this, so hopefully someone knows a solution to my problem.

 

[bbott]

Welcome to the board.

Since your groups don't overlap age ranges (18 & below is red, 19-29 is orange, etc.) you only need to look up age ranges. Assuming your input is in B1:B3, try:

=CHOOSE(MATCH(B3,{0,15,17,19,25,30,33,35,100},1),16,17,18,20,24,26,32,36)*1000

 

[lemony]

Thank you

What if they do overlap?

I realized maybe my example wasn't the best depiction,

It's more like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Red[/TD]
[TD]14-16[/TD]
[TD]$16,000[/TD]
[/TR]
[TR]
[TD]Light Red[/TD]
[TD]14-16[/TD]
[TD]$17,000[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]17-24[/TD]
[TD]$18,000[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]25-29[/TD]
[TD]$20,000[/TD]
[/TR]
[TR]
[TD]Light Orange[/TD]
[TD]17-24[/TD]
[TD]$19,000[/TD]
[/TR]
[TR]
[TD]Light Orange[/TD]
[TD]25-29[/TD]
[TD]$21,000[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]30-32[/TD]
[TD]$26,000[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]33-34[/TD]
[TD]$32,000[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]35+[/TD]
[TD]$36,000[/TD]
[/TR]
[TR]
[TD]Light Blue[/TD]
[TD]30-32[/TD]
[TD]$34,000[/TD]
[/TR]
[TR]
[TD]Light Blue[/TD]
[TD]33-34[/TD]
[TD]$33,000[/TD]
[/TR]
[TR]
[TD]Light Blue[/TD]
[TD]35+[/TD]
[TD]$37,000[/TD]
[/TR]
[TR]
[TD]Grey[/TD]
[TD]All Ages[/TD]
[TD]$20,000[/TD]
[/TR]
</tbody>[/TABLE]

 

[bbott]

Ah. Yes, there are ways to do that. Unfortunately, not my expertise.

I'm sure someone else on this forum will be able to help you out!