Man I have been trying different formulas to get the results I need consistently but cannot get this to work. My sheet calculates the amount of assistance based on family size and income. I have been able to use an INDIRECT fromula provided by DerbyDad03 for other scenarios that only had 2 possible results so an IF statement did the trick but in this case there are 4 possible results (80000, 70000, 60000, or 50000).

G10=family size

F9=income=INDIRECT("I"&G10+21): this formula gets me the correct row for the family size entered by user. But I then have to evaluate the income in F9 and based on where it falls on the chart I want to return the assistance amount in row 21

Data (Column I is the family size, Row 21 is the assistance amount):19 I J K L M 20 50% Median 80% Median 100% Median 140% Median 21 $80,000 $70,000 $60,000 $50,000 22 1 $23,000 $36,750 $46,000 $64,400 23 2 $26,250 $42,000 $52,500 $73,500 24 3 $29,550 $47,250 $59,100 $82,740 25 4 $32,800 $52,500 $65,600 $91,840 26 5 $35,450 $56,700 $70,900 $99,260 27 6 $38,050 $60,900 $76,100 $106,540 28 7 $40,700 $65,100 $81,400 $113,960 29 8 $43,300 $69,300 $86,600 $121,240

This is the best that I could up with for now. There may be an easier way, but that's all I have time for right now. 1 - Reverse your table so that the 140% column is in J and 50% is in M. The MATCH formula uses a

match_typeargument of -1 (Greater Than) so the salary table must be in descending order.2 - The INDIRECT(ADDRESS & ":" & ADDRESS) functions within the MATCH function are used to build the Range for the MATCH function based on the value in G10. e.g. ADDRESS(G10+21,10) for a Family Size of 2 will be J23, therefore the overall Range will be J23:M23.

3 - The "main" INDIRECT(ADDRESS...) functions use the Column number returned by the MATCH function + 9 to return the value from Row 21, Column J, K, L or M.

EDIT: Please see my response below (#6) for a slightly simpler version of this solution.=INDIRECT(ADDRESS(21,9+MATCH(F9,INDIRECT(ADDRESS(G10+21,10)&":"&ADDRESS(G10+21,13)),-1)))

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

message edited by DerbyDad03

Wow that worked perfectly, I would have never been able to come up with that, thank you very much.

I'll admit, it took a couple of Google searches to put that together. I've played with the ADDRESS function before, but never to build a Range reference based on a user supplied value. That was kind of fun.

The main thing that I don't like about that solution is that there are so many hard-coded values that are used as "offsets". That really limits the flexibility you have has far as your sheet layout. Move the Salary Table or insert even one Row or Column before the table and the whole formula has to be modified.

There may be a better way but I can't think of it right now.

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

That is exactly what happened yesterday, once I got the results consistently I began changing the aesthetics of the sheet and had to tweak formula for every change. How about this thought, use the index/match formula to identify the cell address and somehow use the column part of that cell address and couple it with the row where the assistance is since the cell address for the assistance is in the same row but the column changes.

re: " couple it with the row where the assistance is since the cell address for the assistance is in the same row but the column changes"Isn't that what I am already doing?

The first part of the formula says: INDIRECT(ADDRESS(21

That 21 is the Assistance Row in your example data. The rest of the formula determines which Column to use by MATCHing the Salary in F9 within the Row determined by G10+21.

My thought is that the table needs to be a Named Table or at least a Named Range so that if it moves the formula always knows where it is. I don't know that I have time to work on that today or even over the next few days.

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

Regarding the solution that I offered in Response #1... It turns out that the formula I offered is a bit more complicated than it needs to be. Since we know that the result is to be returned from Row # 1, there is no need to use INDIRECT(ADDRESS(21, to obtain the Row address. We can hardcode the 1 and use it as the row_num argument of the array in the INDEX function. Then we only have to find the column_num argument via MATCH:

=INDEX($J$21:$M$29,1,MATCH(F9,INDIRECT(ADDRESS(G10+21,10)&":"&ADDRESS(G10+21,13)),-1))

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

message edited by DerbyDad03

This makes sense and worked just fine, thanks for coming back to this.

Ask Your Question

Weekly Poll

Do you think the longer-range Nissan Leaf will shake up the electric car market?

Discuss in The Lounge

Poll History