# VLOOKUP based on multiple criteria, one is age range

#### lemony

##### New Member
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.

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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

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>

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!

Replies
7
Views
397
Replies
5
Views
492
Replies
6
Views
236
Replies
4
Views
708
Replies
1
Views
858

1,196,325
Messages
6,014,653
Members
441,834
Latest member
GHOSTOF309

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back