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

Hello! Thanks for the reply, I tried it this way but there seems to be an issue with income values larger than 99,999 , as the resulting categories are wrong. I have highlighted the categories that are incorrect in red. DO you know how to solve this? Thanks in advance for the help!

Answer 4

Hello again! I went ahead and tried it this way but there seems to be an error somewhere in my formula. Do you know how to fix it? Thanks in advance!

Answer 5

Hi,

Share the download link of the MS Excel file in which you are facing that error. Let your formulas stay in the file.