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;

Group	Age Range	Member $
Red	10-14	$16,000
Red	15-16	$17,000
Red	17-18	$18,000
Orange	19-24	$20,000
Orange	25-29	$24,000
Blue	30-32	$26,000
Blue	33-34	$32,000
Blue	35+	$36,000

<tbody>
</tbody>

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:

Red	14-16	$16,000
Light Red	14-16	$17,000
Orange	17-24	$18,000
Orange	25-29	$20,000
Light Orange	17-24	$19,000
Light Orange	25-29	$21,000
Blue	30-32	$26,000
Blue	33-34	$32,000
Blue	35+	$36,000
Light Blue	30-32	$34,000
Light Blue	33-34	$33,000
Light Blue	35+	$37,000
Grey	All Ages	$20,000

<tbody>
</tbody>

 

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