match list with another column if within min/max range, assign categories

[sparklerock23]

Hi everyone!

I currently have two data sets. 1) Survey responses- containing household size and income and 2) a federal poverty level matrix - containing household size, min/max income, and poverty level in percentage. And what I want to do is match household size in the survey responses with the corresponding household income brackets to determine which % of poverty level this respondent is in.

So if I use the first data point on sheet 1 as an example, I have a family of 5, with annual income of $100,000. I would then take that, go to the second tab, and see that this range corresponds to row 16, making this respondent in the 300-400%FPL category.

So far, I've been using this formula for every household size, but I'm sure there's a more efficient way of doing this.
=IF(AND(R14<=FPL!$C$19,Q14=1),"<225%",IF(AND(R14<=FPL!$C$20,R14>=FPL!$B$20,Q14=1),"225-300%",IF(AND(R14<=FPL!$C$21,R14>=FPL!$B$21,Q14=1),"300-400%", etc.

Thank you for reading!!

[Bo_Ry]

Please try at C2

=LOOKUP(2,1/('FPL Guideline'!$A$2:$A$43=A2)/('FPL Guideline'!$B$2:$B$43<=B2)/('FPL Guideline'!$C$2:$C$43>B2),'FPL Guideline'!$D$2:$D$43)

Row 4 shows #N/A because 4 People Annual Income $100,440 is more than maximum in guideline row 16 100,400.
You may need to increase maximum or add data for more than 100,400.

[FlameRetired]

Bo check me on this. I don't think we need column C Max. Which means your formula is fine. Just do this with it.

Please Login or Register  to view this content.

If the greatest Min for family of 4 is less than income that's all we need. Anything greater than the Max seems to me irrelevant. Everything off scale is in the last % FPL. Makes me wonder why any Max exists for the last family/% FPL category.

[Bo_Ry]

Dave, you're right. We don't need Max

[FlameRetired]

_______________

[sparklerock23]

Thank you so much for your help!!!

[FlameRetired]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.