✔ 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

First, a posting tip: Please click on the

How-To linkat 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

Thanks DerbyDad03, that was quite useful and looks so much clearer :-)

someone making $28,500someone making $61,000Where are you getting these numbers, they are not indicated on your table?

Column heading might be helpful.

MIKE

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

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

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

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

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

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.00Assuming (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

Just curious, what happens if

a family of 1 makes less than 26450 or

a family of 2 makes less than 30200?MIKE

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

DerbyDad03 you are absolutely right, duly noted for future posts.

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

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

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History