Solved If statement or Index/match help

June 28, 2018 at 13:20:03
Specs: Windows 7
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 


See More: If statement or Index/match help

Reply ↓  Report •

#1
June 28, 2018 at 14:51:03
✔ Best Answer
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_type argument 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


Reply ↓  Report •

#2
June 28, 2018 at 19:26:59
Wow that worked perfectly, I would have never been able to come up with that, thank you very much.

Reply ↓  Report •

#3
June 28, 2018 at 20:18:31
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


Reply ↓  Report •

Related Solutions

#4
June 29, 2018 at 04:37:07
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.


Reply ↓  Report •

#5
June 29, 2018 at 05:31:41
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


Reply ↓  Report •

#6
July 2, 2018 at 10:02:09
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


Reply ↓  Report •

#7
July 2, 2018 at 13:59:36
This makes sense and worked just fine, thanks for coming back to this.

Reply ↓  Report •

Ask Question