Multiple Criteria, Multiple Column Look Up Range

[TwoFun4Me]

I know there MUST be a better way to do this that a ton of nesting vlookup and if/then statements, but I can't figure it out. I've looked at arrays, index/match...I cant figure it out.

Ok, so I have a business with sliding scale rates based on the number of people in a household and the household wages. Based on this, we find a rate: Here is an image if how we get the rate...then the data if you want to test your fix.

[TABLE="class: grid, width: 1217"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 10"] HOUSEHOLD SIZE [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Classroom Max Age[/TD]
[TD] 2-Min [/TD]
[TD] 2-Max [/TD]
[TD]3-Min[/TD]
[TD]3-Max[/TD]
[TD]4-Min[/TD]
[TD]4-Max[/TD]
[TD]5-Min[/TD]
[TD]5-Max[/TD]
[TD]6-Min[/TD]
[TD]6-Max[/TD]
[TD]Current Weekly Rates[/TD]
[TD]New Weekly Rates[/TD]
[TD] [/TD]
[TD]# in HH[/TD]
[TD]Age in Months[/TD]
[TD]Annual Income[/TD]
[TD]Rate Result Should Be[/TD]
[/TR]
[TR]
[TD]6 Wks - 18Mo[/TD]
[TD] $ - [/TD]
[TD] $ 16,900[/TD]
[TD] $ - [/TD]
[TD] $ 19,000[/TD]
[TD] $ - [/TD]
[TD] $ 21,100[/TD]
[TD] $ - [/TD]
[TD] $ 22,800[/TD]
[TD] $ - [/TD]
[TD] $ 24,500[/TD]
[TD] $ 100[/TD]
[TD] $ 80[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]22[/TD]
[TD] $ 38,196[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 16,901[/TD]
[TD] $ 28,200[/TD]
[TD] $ 19,001[/TD]
[TD] $ 31,700[/TD]
[TD] $ 21,101[/TD]
[TD] $ 35,200[/TD]
[TD] $ 22,801[/TD]
[TD] $ 38,050[/TD]
[TD] $ 24,501[/TD]
[TD] $ 40,850[/TD]
[TD] $ 110[/TD]
[TD] $ 95[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD] $ 38,480[/TD]
[TD="align: right"]95[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD] $ 28,201[/TD]
[TD] $ 36,650[/TD]
[TD] $ 31,701[/TD]
[TD] $ 41,250[/TD]
[TD] $ 35,201[/TD]
[TD] $ 45,800[/TD]
[TD] $ 38,051[/TD]
[TD] $ 49,500[/TD]
[TD] $ 40,851[/TD]
[TD] $ 53,150[/TD]
[TD] $ 120[/TD]
[TD] $ 110[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD] $ 44,713[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 36,651[/TD]
[TD] $ 45,050[/TD]
[TD] $ 41,251[/TD]
[TD] $ 50,700[/TD]
[TD] $ 45,801[/TD]
[TD] $ 56,300[/TD]
[TD] $ 49,501[/TD]
[TD] $ 60,850[/TD]
[TD] $ 53,151[/TD]
[TD] $ 65,350[/TD]
[TD] $ 130[/TD]
[TD] $ 130[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]
[TD] $ 56,148[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 45,051[/TD]
[TD] $ 57,023[/TD]
[TD] $ 50,701[/TD]
[TD] $ 59,023[/TD]
[TD] $ 56,301[/TD]
[TD] $ 65,800[/TD]
[TD] $ 60,851[/TD]
[TD] $ 69,500[/TD]
[TD] $ 65,351[/TD]
[TD] $ 71,500[/TD]
[TD] $ 130[/TD]
[TD] $ 150[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]16[/TD]
[TD] $ 47,976[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 57,024[/TD]
[TD] $ 150,000[/TD]
[TD] $ 59,024[/TD]
[TD] $ 152,000[/TD]
[TD] $ 65,801[/TD]
[TD] $ 154,500[/TD]
[TD] $ 69,501[/TD]
[TD] $ 156,500[/TD]
[TD] $ 71,501[/TD]
[TD] $ 158,500[/TD]
[TD] $ 130[/TD]
[TD] $ 175[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]42[/TD]
[TD] $ 22,200[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]19Mo to 23Mo[/TD]
[TD] $ - [/TD]
[TD] $ 16,900[/TD]
[TD] $ - [/TD]
[TD] $ 19,000[/TD]
[TD] $ - [/TD]
[TD] $ 21,100[/TD]
[TD] $ - [/TD]
[TD] $ 22,800[/TD]
[TD] $ - [/TD]
[TD] $ 24,500[/TD]
[TD] $ 100[/TD]
[TD] $ 75[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]51[/TD]
[TD] $ 120,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 16,901[/TD]
[TD] $ 28,200[/TD]
[TD] $ 19,001[/TD]
[TD] $ 31,700[/TD]
[TD] $ 21,101[/TD]
[TD] $ 35,200[/TD]
[TD] $ 22,801[/TD]
[TD] $ 38,050[/TD]
[TD] $ 24,501[/TD]
[TD] $ 40,850[/TD]
[TD] $ 110[/TD]
[TD] $ 90[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]17[/TD]
[TD] $ 12,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD] $ 28,201[/TD]
[TD] $ 36,650[/TD]
[TD] $ 31,701[/TD]
[TD] $ 41,250[/TD]
[TD] $ 35,201[/TD]
[TD] $ 45,800[/TD]
[TD] $ 38,051[/TD]
[TD] $ 49,500[/TD]
[TD] $ 40,851[/TD]
[TD] $ 53,150[/TD]
[TD] $ 120[/TD]
[TD] $ 105[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]35[/TD]
[TD] $ 40,560[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 36,651[/TD]
[TD] $ 45,050[/TD]
[TD] $ 41,251[/TD]
[TD] $ 50,700[/TD]
[TD] $ 45,801[/TD]
[TD] $ 56,300[/TD]
[TD] $ 49,501[/TD]
[TD] $ 60,850[/TD]
[TD] $ 53,151[/TD]
[TD] $ 65,350[/TD]
[TD] $ 130[/TD]
[TD] $ 125[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]47[/TD]
[TD] $ 31,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 45,051[/TD]
[TD] $ 57,023[/TD]
[TD] $ 50,701[/TD]
[TD] $ 59,023[/TD]
[TD] $ 56,301[/TD]
[TD] $ 65,800[/TD]
[TD] $ 60,851[/TD]
[TD] $ 69,500[/TD]
[TD] $ 65,351[/TD]
[TD] $ 71,500[/TD]
[TD] $ 130[/TD]
[TD] $ 145[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]55[/TD]
[TD] $ 43,139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 57,024[/TD]
[TD] $ 150,000[/TD]
[TD] $ 59,024[/TD]
[TD] $ 152,000[/TD]
[TD] $ 65,801[/TD]
[TD] $ 154,500[/TD]
[TD] $ 69,501[/TD]
[TD] $ 156,500[/TD]
[TD] $ 71,501[/TD]
[TD] $ 158,500[/TD]
[TD] $ 130[/TD]
[TD] $ 170[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]32[/TD]
[TD] $ 52,800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24Mo - 35Mo[/TD]
[TD] $ - [/TD]
[TD] $ 16,900[/TD]
[TD] $ - [/TD]
[TD] $ 19,000[/TD]
[TD] $ - [/TD]
[TD] $ 21,100[/TD]
[TD] $ - [/TD]
[TD] $ 22,800[/TD]
[TD] $ - [/TD]
[TD] $ 24,500[/TD]
[TD] $ 85[/TD]
[TD] $ 70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 16,901[/TD]
[TD] $ 28,200[/TD]
[TD] $ 19,001[/TD]
[TD] $ 31,700[/TD]
[TD] $ 21,101[/TD]
[TD] $ 35,200[/TD]
[TD] $ 22,801[/TD]
[TD] $ 38,050[/TD]
[TD] $ 24,501[/TD]
[TD] $ 40,850[/TD]
[TD] $ 92[/TD]
[TD] $ 85[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD] $ 28,201[/TD]
[TD] $ 36,650[/TD]
[TD] $ 31,701[/TD]
[TD] $ 41,250[/TD]
[TD] $ 35,201[/TD]
[TD] $ 45,800[/TD]
[TD] $ 38,051[/TD]
[TD] $ 49,500[/TD]
[TD] $ 40,851[/TD]
[TD] $ 53,150[/TD]
[TD] $ 99[/TD]
[TD] $ 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 36,651[/TD]
[TD] $ 45,050[/TD]
[TD] $ 41,251[/TD]
[TD] $ 50,700[/TD]
[TD] $ 45,801[/TD]
[TD] $ 56,300[/TD]
[TD] $ 49,501[/TD]
[TD] $ 60,850[/TD]
[TD] $ 53,151[/TD]
[TD] $ 65,350[/TD]
[TD] $ 105[/TD]
[TD] $ 120[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 45,051[/TD]
[TD] $ 57,023[/TD]
[TD] $ 50,701[/TD]
[TD] $ 59,023[/TD]
[TD] $ 56,301[/TD]
[TD] $ 65,800[/TD]
[TD] $ 60,851[/TD]
[TD] $ 69,500[/TD]
[TD] $ 65,351[/TD]
[TD] $ 71,500[/TD]
[TD] $ 105[/TD]
[TD] $ 140[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 57,024[/TD]
[TD] $ 150,000[/TD]
[TD] $ 59,024[/TD]
[TD] $ 152,000[/TD]
[TD] $ 65,801[/TD]
[TD] $ 154,500[/TD]
[TD] $ 69,501[/TD]
[TD] $ 156,500[/TD]
[TD] $ 71,501[/TD]
[TD] $ 158,500[/TD]
[TD] $ 105[/TD]
[TD] $ 165[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36Mo - 47Mo[/TD]
[TD] $ - [/TD]
[TD] $ 16,900[/TD]
[TD] $ - [/TD]
[TD] $ 19,000[/TD]
[TD] $ - [/TD]
[TD] $ 21,100[/TD]
[TD] $ - [/TD]
[TD] $ 22,800[/TD]
[TD] $ - [/TD]
[TD] $ 24,500[/TD]
[TD] $ 80[/TD]
[TD] $ 65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 16,901[/TD]
[TD] $ 28,200[/TD]
[TD] $ 19,001[/TD]
[TD] $ 31,700[/TD]
[TD] $ 21,101[/TD]
[TD] $ 35,200[/TD]
[TD] $ 22,801[/TD]
[TD] $ 38,050[/TD]
[TD] $ 24,501[/TD]
[TD] $ 40,850[/TD]
[TD] $ 85[/TD]
[TD] $ 80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD] $ 28,201[/TD]
[TD] $ 36,650[/TD]
[TD] $ 31,701[/TD]
[TD] $ 41,250[/TD]
[TD] $ 35,201[/TD]
[TD] $ 45,800[/TD]
[TD] $ 38,051[/TD]
[TD] $ 49,500[/TD]
[TD] $ 40,851[/TD]
[TD] $ 53,150[/TD]
[TD] $ 95[/TD]
[TD] $ 95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 36,651[/TD]
[TD] $ 45,050[/TD]
[TD] $ 41,251[/TD]
[TD] $ 50,700[/TD]
[TD] $ 45,801[/TD]
[TD] $ 56,300[/TD]
[TD] $ 49,501[/TD]
[TD] $ 60,850[/TD]
[TD] $ 53,151[/TD]
[TD] $ 65,350[/TD]
[TD] $ 100[/TD]
[TD] $ 115[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 45,051[/TD]
[TD] $ 57,023[/TD]
[TD] $ 50,701[/TD]
[TD] $ 59,023[/TD]
[TD] $ 56,301[/TD]
[TD] $ 65,800[/TD]
[TD] $ 60,851[/TD]
[TD] $ 69,500[/TD]
[TD] $ 65,351[/TD]
[TD] $ 71,500[/TD]
[TD] $ 100[/TD]
[TD] $ 135[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 57,024[/TD]
[TD] $ 150,000[/TD]
[TD] $ 59,024[/TD]
[TD] $ 152,000[/TD]
[TD] $ 65,801[/TD]
[TD] $ 154,500[/TD]
[TD] $ 69,501[/TD]
[TD] $ 156,500[/TD]
[TD] $ 71,501[/TD]
[TD] $ 158,500[/TD]
[TD] $ 100[/TD]
[TD] $ 160[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pre-K: Gonzalez *[/TD]
[TD] $ - [/TD]
[TD] $ 16,900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]19000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21100[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]24500[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]48Mo - 60Mo[/TD]
[TD] $ 16,901[/TD]
[TD] $ 28,200[/TD]
[TD="align: right"]19001[/TD]
[TD="align: right"]31700[/TD]
[TD="align: right"]21101[/TD]
[TD="align: right"]35200[/TD]
[TD="align: right"]22801[/TD]
[TD] [/TD]
[TD="align: right"]24501[/TD]
[TD="align: right"]40850[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD] $ 28,201[/TD]
[TD] $ 36,650[/TD]
[TD="align: right"]31701[/TD]
[TD="align: right"]41250[/TD]
[TD="align: right"]35201[/TD]
[TD="align: right"]45800[/TD]
[TD="align: right"]38051[/TD]
[TD] [/TD]
[TD="align: right"]40851[/TD]
[TD="align: right"]53150[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 36,651[/TD]
[TD] $ 45,050[/TD]
[TD="align: right"]41251[/TD]
[TD="align: right"]50700[/TD]
[TD="align: right"]45801[/TD]
[TD="align: right"]56300[/TD]
[TD="align: right"]49501[/TD]
[TD] [/TD]
[TD="align: right"]53151[/TD]
[TD="align: right"]65350[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]* Non-Grandfathered Kids, parents ARE paying Gonzalez rates[/TD]
[TD] $ 45,051[/TD]
[TD] $ 57,023[/TD]
[TD="align: right"]50701[/TD]
[TD="align: right"]59023[/TD]
[TD="align: right"]56301[/TD]
[TD="align: right"]65800[/TD]
[TD="align: right"]60851[/TD]
[TD] [/TD]
[TD="align: right"]65351[/TD]
[TD="align: right"]71500[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $ 57,024[/TD]
[TD] $ 150,000[/TD]
[TD="align: right"]59024[/TD]
[TD="align: right"]152000[/TD]
[TD="align: right"]65801[/TD]
[TD="align: right"]154500[/TD]
[TD="align: right"]69501[/TD]
[TD] [/TD]
[TD="align: right"]71501[/TD]
[TD="align: right"]158500[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Here is one of the nested statements I've used...but have to change up for each household size and classroom max age size:

=IF(AND(H30>=6,I30<=$Q$11),VLOOKUP(J30,$Z$9:$AK$14,4),IF(AND(H30>=6,I30<=$Q$17),VLOOKUP(J30,$Z$15:$AK$20,4),IF(AND(H30>=6,I30<=$Q$23),VLOOKUP(J30,$Z$21:$AK$26,4),IF(AND(H30>=6,I30<=$Q$29),VLOOKUP(J30,$Z$27:$AK$32,4),IF(AND(H30>=6,I30<=$Q$35),VLOOKUP(J30,$Z$33:$AK$38,4),"Next HH")))))

There must be an easier way???

Thanks.

 

[NeonRedSharpie]

Ok, so I figured out a way without VBA, but VBA would be quite a bit easier.

=IFERROR(INDIRECT(MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",MATCH("New Weekly Rates",$A$2:$S$2,0),1)&MATCH($R3,INDIRECT(MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",MATCH($P3&"-Max",$A$2:$L$2,0),1)&MATCH(INDEX(ageBuckets,MATCH($Q3,age,0))&"-Min",$A$1:$A$32,0)&":"&MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",MATCH($P3&"-Max",$A$2:$L$2,0),1)&MATCH(INDEX(ageBuckets,MATCH($Q3,age,0))&"-Max",$A$1:$A$32,0)),1)+MATCH(INDEX(ageBuckets,MATCH($Q3,age,0))&"-Min",$A$1:$A$32,0)),"Please Manually Check")

Here is the code I came up with. It requires adding 1 helper column, as well as two named ranges.

[age] and [ageBuckets] are crosswalk columns.

0-18
1-18
2-18
3-18
4-18
5-18
...
18-18
19-23
20-23
21-23
22-23
23-23
24-35
...
34-35
35-35

The left column is the lookup [age] and the right column is the corresponding [ageBucket].
I had to add a column to the left of the table that goes from 18-Min to 18-Max. 18-Min is row 3, 18-Max is row 8 (in the example).

The new column is column A, while the existing table begins at column B.

A2:S2 is the header row
A1:A32 is the age bucket helper column
P3 is the household number
Q3 is the age in months
R3 is the annual income

 

[Eric W]

I pasted your sample sheet into a workbook, and the range was A1:R33. The "Rate Result Should Be" was column R. Put this formula in R4, then copy and paste it down:

=INDEX(OFFSET($M$4,(MATCH(P4,{1,19,24,36,48})-1)*6,0,6,1),IFERROR(MATCH(Q4,OFFSET($B$4,(MATCH(P4,{1,19,24,36,48})-1)*6,(O4-2)*2,6,1)),1))

It works for all your sample data down to O13. The examples with 7 and 8 in the household don't work properly since your table doesn't have 7 or 8 in them. If you intend them to use the 6-Min/Max columns for anything over 6, then that can be adapted into the formula.

Let me know how that works.

 

[TwoFun4Me]

I tried the second option, as it seemed a little less complicated, and it worked great!! I so appreciate your help. I do alot of this lookup with sub lookups and really needed a good tool to use. Now, I need to decipher the code so I understand it!

Thank you so much!

 

[Eric W]

You're welcome!

You can run it through the "Evaluate Formula" tool to see how it works. If you still have questions, let us know.

Have a Happy New Year!