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 amount

My table structure:
F column has the household size
Income range is G5:M12
assistance is G4:M4

Example:

	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,250
or
someone 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

Reply ↓  Report •

✔ Best Answer
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))

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

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!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

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

Reply ↓  Report •

#3
June 25, 2018 at 09:46:32
someone making $28,500
someone making $61,000

Where are you getting these numbers, they are not indicated on your table?

Column heading might be helpful.

MIKE

http://www.skeptic.com/


Reply ↓  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)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  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


Reply ↓  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,750
family size of 2 can make up to $48,350 to get $7,250 or between $42,350.01 and $84,560 and get $4,750
family size of 3 can make up to $54,400 to get $7,250 or between $54,400.01 and $95,200 and get $4,750
family size of 4 can make up to $60,400 to get $7,250 or between $60,400.01 and $105,7000 and get $4,750
family size of 5 can make up to $65,250 to get $7,250 or between $65,250.01 and $114,240 and get $4,750
family size of 6 can make up to $70,100 to get $7,250 or between $70,100.01 and $122,640 and get $4,750
family size of 7 can make up to $74,900 to get $7,250 or between $74,900.01 and $131,180 and get $4,750
family 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

Reply ↓  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


Reply ↓  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<I20),7250,IF(AND(G15=2,G16<I21),7250,IF(AND(G15=3,G16<I22),7250,
IF(AND(G15=4,G16<I23),7250,IF(AND(G15=5,G16<I24),7250,IF(AND(G15=6,G16<I25),7250,
IF(AND(G15=7,G16<I26),7250,IF(AND(G15=8,G16<I27),7250,4750))))))))

Where G15=Family Size
G16=Income
Column I is top end of income for assistance of $7,250


Reply ↓  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 C31
2 - Salary in D31
3 - 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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  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?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#11
June 25, 2018 at 20:26:09
This response has nothing to do with your actual question, it's more about your posting methods. Please accept this in the spirit that it is intended.

Let's take a look at your original post and your Posts #5, 7 & 8.

In your original post, you used column letters A:H.

In Post # 5. you posted the full table without any column letters or row numbers. We are left to assume (dangerous) that you are still using A:H.

In Post # 7, you posted a smaller table, again without any column letters or row numbers. We are left to assume (dangerous) that you are now using A:E. You also said: "search for the family size from C31"

However, in Post # 8 you said the Family Size is in G15 (What happened to C31?) and "Column I is top end of income" (Where the heck did Column I come from?)

Since we have to set up test sheets in order to try out solutions, it would help us help you if you provided accurate and consistent information. Obviously, if your data table can be made more concise or something like that, the changes make sense. However, when the formula you post as the solution you came up has cell references that were never seen before in the thread, things get very confusing for us out here on the ole interweb. Please keep in mind that we can't see your worksheet from where we're sitting, so the only thing we have to work with is what you post. When that info is incomplete or inconsistent, it makes it hard for us to follow along. We want to help, so you should try to make it as easy as possible for us.to do that.

Thanks for listening!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

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

Reply ↓  Report •

#13
June 26, 2018 at 05:24:16
✔ Best Answer
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))

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#14
June 26, 2018 at 10:03:47
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


Reply ↓  Report •

Ask Question