Excel how to put data into categories with multiple requirements and a range of values for one requirement

Question

Hello, 

I need help with finding the right formula for Excel to put data into categories. The category that it needs to be in depends on two factors: household size and income. First, I need it to understand how many people are in the household (a number between 1 and 8, so an exact match), then within that category, I need it to return the income category (extremely low, very low, low or high) based on income ranges (so not an exact match, which is what I am having difficulty with). I have figured out how to reference two requirements if they are BOTH an exact match, but the income ranges will not be an exact match. I need it to reference income limits. I have made multiple tables in hopes the table structure was the problem. I have also tried putting all the income limits in ascending order but that also has not worked. I have tried using VLOOKUP, INDEX, and MATCH but none of them work for my specific case. For some reason my screenshots are not uploading no matter what I try, so I hope this can be solved without screenshots. I made a table below for some reference: 

This is the table that would need to be referenced to put the data into an income range category (ex. low, very low, low, high) 

there really isn't an upper limit for high, because anything about the limit for low is considered high. 

Household size	Extremely Low Income upper limit	Very Low	Low	High
1	15,550	25,990	41,400	300,000
2	17,750	29,600	47,300	300,000
3	21,720	33,300	53,200	300,000

Here is another table to show an example of what the data looks like, where I need a formula to generate the income category. 

Household Size	Income	Income Category
2	50,000
4	47,250
1	32,800

I'm sure there has to be a way for it to find a category based on one requirement that is an exact match and one requirement that has limits/ranges. Please let me know!

Answer 1

Hi,

If you are Ok with transforming your first table like the one shown in the image below, then this formula will work in cell H2

=XLOOKUP(F2&G2,$A$2:$A$33&$B$2:$B$33,$D$2:$D$33,"",-1)

Hope this helps.

Answer 2

Do I just need to change the ranges to the minimum value instead of the maximum income value?

Exactly!

Same file.

Answer 3

https://liveutk-my.sharepoint.com/:x:/g/personal/pbg457_vols_utk_edu/EfoAICkUA6BLpmrbaSOUtjEBFQFn50b_FRT7K1B7v0QXzQ?e=p9Sw6d

I am not sure how to make it to allow changes from other people so I hope just viewing the file will allow you to help me. If not, let me know how I can change that on a Mac. Thanks!!

Answer 4

Hi,

In cell I4, enter this formula

=IFERROR(INDEX($D$4:$D$35,MATCH(1,INDEX(($A$4:$A$35=G4)*($B$4:$B$35<=H4)*($C$4:$C$35>+H4),,),0),1),"")

Hope this helps.