# Solved How to return a value using 2 criteria and numeric range

June 25, 2018 at 08:16:50
Specs: Windows 7
 Hello forum experts, need some help with a small project.I have a data table that includes family size, income ranges and assistance amount. I need to return the assistance amount based on 2 criteria: Family size and Income amountMy table structure:F column has the household sizeIncome range is G5:M12assistance is G4:M4Example:``` A B C D E F G H 1 7,250 7,250 7,250 4,750 4,750 4,750 4,750 2 1 26450 26450.01 42300 42300.01 52900 52900.01 74060 3 2 30200 30200.01 48350 48350.01 60400 60400.01 84560 ```So in the table above I would locate family size (column A) then find income (Columns B-H) which can fall in between a range and once I know where it falls I would return the assistance amount in the first row (row 1 7,250 or 4,750). so someone making \$28,500 with a family size of 2 would get \$7,250orsomeone making \$61,000 with a family size of 1 would get \$4,750.I have tried variations of match and index and vlookup but can't seem to find a formula that can do it all.any help would be appreciated.message edited by mecerrato

See More: How to return a value using 2 criteria and numeric range

June 26, 2018 at 05:24:16
 Thanks for your understanding.Can you answer the other questions that Mike and I have asked?Specifically, why is there a low end on your table of salaries? e.g. What happens if a family of 1 makes less than \$26,450, etc?Did you try my INDIRECT option? It's much shorter than your long IF and also more adaptable to other salary/assistance data sets.e.g. use all cell references instead of hardcoded values:=IF(D31>INDIRECT("E" & C31+1),0,IF(D31>INDIRECT("C" & C31+1), A1, B1))message edited by DerbyDad03

#1
June 25, 2018 at 08:18:41
 First, a posting tip: Please click on the How-To link at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example. Thanks!

Report •

#2
June 25, 2018 at 09:16:22
 Thanks DerbyDad03, that was quite useful and looks so much clearer :-)

Report •

#3
June 25, 2018 at 09:46:32
 someone making \$28,500 someone making \$61,000Where are you getting these numbers, they are not indicated on your table?Column heading might be helpful.MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
June 25, 2018 at 10:50:10
 I don't understand why the ranges are different for each family. Family 2 can make up to \$48,350 and get \$7,250 but Family 1 can only make \$43,250? That doesn't seem fair.Regardless, your breakpoint seems to be Column D. It seems like a simple IF function is all that is needed.=IF(salary > Dx, 4750, 7250)

Report •

#5
June 25, 2018 at 15:59:56
 DerbyDad03, I only provided part of the table, the table goes up to family size of 8. The bigger the family size is the more assistance is provided that is why a family size of 2 gets \$7,250 in that example and family size of 1 is maxed out at \$42,300 to get \$7,250 otherwise they get \$4,750.the if formula doesn't work because of the rest of the table, full table:``` 7,250 7,250 7,250 4,750 4,750 4,750 4,750 1 26450 26450.01 42300 42300.01 52900 52900.01 74060 2 30200 30200.01 48350 48350.01 60400 60400.01 84560 3 34000 34000.01 54400 54400.01 68000 68000.01 95200 4 37750 37750.01 60400 60400.01 75500 75500.01 105700 5 40800 40800.01 65250 65250.01 81600 81600.01 114240 6 43800 43800.01 70100 70100.01 87600 87600.01 122640 7 46850 46850.01 74900 74900.01 93700 93700.01 131180 8 49850 49850.01 79750 79750.01 99700 99700.01 139580 ```

Report •

#6
June 25, 2018 at 16:07:23
 mmcconaghy those numbers are income of the family and they would fall in between the ranges.so column 1 is the family size and the income maximums are the next 7 columns.family size of 1 can make up to \$42,300 to get \$7,250 or between \$42,300.01 and \$74,060 and get \$4,750family size of 2 can make up to \$48,350 to get \$7,250 or between \$42,350.01 and \$84,560 and get \$4,750family size of 3 can make up to \$54,400 to get \$7,250 or between \$54,400.01 and \$95,200 and get \$4,750family size of 4 can make up to \$60,400 to get \$7,250 or between \$60,400.01 and \$105,7000 and get \$4,750family size of 5 can make up to \$65,250 to get \$7,250 or between \$65,250.01 and \$114,240 and get \$4,750family size of 6 can make up to \$70,100 to get \$7,250 or between \$70,100.01 and \$122,640 and get \$4,750family size of 7 can make up to \$74,900 to get \$7,250 or between \$74,900.01 and \$131,180 and get \$4,750family size of 8 can make up to \$79,750 to get \$7,250 or between \$79,750.01 and \$139,580 and get \$4,750

Report •

#7
June 25, 2018 at 16:11:28
 Actually thinking this through some more I have reduced my table and only included the maximums for each family size, so the table looks like this:``` 7,250 7,250 4,750 4,750 1 26450 42300 42300.01 74060 2 30200 48350 48350.01 84560 3 34000 54400 54400.01 95200 4 37750 60400 60400.01 105700 5 40800 65250 65250.01 114240 6 43800 70100 70100.01 122640 7 46850 74900 74900.01 131180 8 49850 79750 79750.01 139580 ```So the lookup formula or index/match formula would search for the family size from C31 (the input section) to know what row to evaluate the next criteria which is income. Then it should find what column the income falls under and return the assistance amount in the first row (assistance amount row).message edited by mecerrato

Report •

#8
June 25, 2018 at 17:35:00
 I was able to get it working with a long IF statement but I figured there has to be a more efficient way. I will be doing this with multiple income tables so I would prefer a cleaner way of doing it to avoid human error:```=IF(AND(G15=1,G16

Report •

#9
June 25, 2018 at 17:59:19
 Please include Column letters and Row numbers whenever you post example data. Don't make us go back to previous posts to figure out what range your table is in. Your job is to make as easy as possible for us to offer solutions.That said, I'm still somewhat confused. You said that you "only included the maximums for each family size" yet I see a low end and a high end for each.Why is there a low end for \$7250? e.g. Does a family of 1 that makes under \$26,450 get nothing?Why is there a low end for \$4,750? Isn't it a given that anything above the Column C (?) values, up to the Column E (?) values gets \$4,750 (assuming I got the Column letters correct for your latest data set.)OK, let's try this:If this is your table, even though I don't know why you have Column B or D, try the formula below``` A B C D E 1 7,250 7,250 4,750 4,750 2 1 26450 42300 42300.01 74060 3 2 30200 48350 48350.01 84560 4 3 34000 54400 54400.01 95200 5 4 37750 60400 60400.01 105700 6 5 40800 65250 65250.01 114240 7 6 43800 70100 70100.01 122640 8 7 46850 74900 74900.01 131180 9 8 49850 79750 79750.01 139580 ....... 31 1 70150.00 ```Assuming (dangerous):1 - Family Size in C312 - Salary in D313 - Salaries below the Column C values get \$7,250. i.e. No minimum salary (Easily dealt with if that is wrong)Try this:=IF(D31>INDIRECT("E" & C31+1),0,IF(D31>INDIRECT("C" & C31+1), 4750, 7250))The INDIRECT function builds a Range Reference from a text string in a cell. If C31 contains a number (Family Size) the INDIRECT function will use that as a Row number, after 1 is added to it.For example, a 5 in C31 will cause the INDIRECT function to return C6 and E6 since 6 in the Row for a family of 5. After that, it's just a basic IF function using Column C as the break point and Column E as the maximum value above which no assistance is offered.

Report •

#10
June 25, 2018 at 18:27:09
 Just curious, what happens if a family of 1 makes less than 26450 or a family of 2 makes less than 30200?MIKEhttp://www.skeptic.com/

Report •

#11
June 25, 2018 at 20:26:09

Report •

#12
June 26, 2018 at 04:26:17
 DerbyDad03 you are absolutely right, duly noted for future posts.

Report •

#13
June 26, 2018 at 05:24:16
 As always you discovered something I should have caught; there are really only 2 important columns in this table, the max for the 7250 amount of assistance and the max for the 4750 amount of assistance so that being said, this is the correct table and YES the indirect formula did work:``` A B C 1 7,250 4,750 2 1 42300 74060 3 2 48350 84560 4 3 54400 95200 5 4 60400 105700 6 5 65250 114240 7 6 70100 122640 8 7 74900 131180 9 8 79750 139580 ```