Formula to return value based on 2 criteria in the same row

July 2, 2018 at 07:13:30
Specs: Windows 7
Arghhh.... Can't seem to figure out what appears to be a simple formula but I can't find a formula that gives me what I want, the VLOOKUP, HLOOKUP and INDEX/MATCH come close but are designed to give slightly different results.

I need to get the value of a cell based on 2 criteria:
criteria 1 = family size located in H12
criteria 2 = income amount located in G11
the value I want is at intersection where they meet on the same row

Here is the table:

	J	   K       L       M       N       O        P
54 Family Size						
55	1	$13,500	$23,000	$36,750	$46,000	$55,200	 $64,400
56	2	$15,800	$26,250	$42,000	$52,500	$63,000	 $73,500
57	3	$17,750	$29,550	$47,250	$59,100	$70,920	 $82,740
58	4	$19,700	$32,800	$52,500	$65,600	$78,720	 $91,840
59	5	$21,300	$35,450	$56,700	$70,900	$85,080	 $99,260
60	6	$22,900	$38,050	$60,900	$76,100	$91,320	 $106,540
61	7	$24,450	$40,700	$65,100	$81,400	$97,680	 $113,960
62	8	$26,050	$43,300	$69,300	$86,600	$103,920 $121,240

I am using this formula to identify the row along with the column range:

=ADDRESS(H12+54,11)&":"&ADDRESS(H12+54,16)

but I don't know what formula to use to return the value of the cell that the income closely matches to.
for example a family size of 6 that makes $55,000 should return the value $60,900 (value in M60)


See More: Formula to return value based on 2 criteria in the same row

Reply ↓  Report •

#1
July 2, 2018 at 08:00:08
Do really mean "closest to"? In all of your other questions you've used a "maximum value" when comparing salaries.

In this case are you saying that $49,475 should return $60,900 while $49,474 should return $38,051?

That's what "closest to" would mean.

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


Reply ↓  Report •

#2
July 2, 2018 at 08:13:21
Sorry you are right it should be maximum value.

Reply ↓  Report •

#3
July 2, 2018 at 10:16:38
In that case, I essentially gave you the answer to this question in an earlier thread.

https://www.computing.net/answers/o...

You can use the MATCH function to return a value that is Equal To or Greater Than your lookup_value by using a match_type of -1. The criteria for that is that the values in the table must be in descending order, as I noted in that earlier thread.

For a full explanation of the match_type argument, see here:

https://support.office.com/en-us/ar...

So, once again, reverse your table of salaries so that the highest salaries are in Column K, descending to down to P.

Then use this formula to extract the salary that is Equal To or Greater Than the value in G11:

=INDEX($K$55:$P$62,MATCH(H12,$J$55:$J$62,0),
MATCH(G11,INDIRECT(ADDRESS(H12+54,11)&":"&ADDRESS(H12+54,16)),-1))

The main difference between this formula and the formula in the earlier thread is that the Row to return the value from was fixed (21) and only the Column had to be found. In this formula, both the Row and Column need to be found, so there are 2 Match functions required.

2 additional items:

1 - If you don't like the thought of setting up your table to be in descending order just so that you can uses a match_type of -1 (either because the data has to be in ascending order for other calculations or you just don't like the aesthetics) you can always create a "helper table" someplace else in your workbook that will be a mirror image of the real table and refer to the mirror image in your formula. e.g.

If you have this:

         A     B     C
1        1     2     3
2        4     5     6

Then this will be a mirror image:

          A       B        C
4        =C1     =B1      =A1
5        =C2     =B2      =A2

You can make changes to the A1:C2 table and they will be reflected in the A4:C5 table.

2 - After playing with this question for a bit, I realized that there was a slightly easier solution for your earlier question. Please refer back to this thread to see an solution using the INDEX function in Response #6:

https://www.computing.net/answers/o...


message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
July 2, 2018 at 14:02:22
Thanks for the always incredible and helpful responses. My mistake in this one was not reversing the table, I played around a lot with variations of the formulas you have provided and perhaps one of them would have worked if I had remembered to reverse the tables. I totally forgot about the descending order requirement for the match formula.

message edited by mecerrato


Reply ↓  Report •

#5
July 2, 2018 at 14:44:43
I'm glad it worked, but there has to be a way to eliminate the hard coded references, such as the 54 and 16 in this: (H12+54,16)

I'm thinking that if the table was an actual Excel Table, we could reference rows and columns within the Table as opposed to Rows and Columns in the spreadsheet. That way you could drag stuff anywhere you wanted, insert rows, insert columns, whatever and it wouldn't break the formulas.

I'll keep playing...

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


Reply ↓  Report •

#6
July 4, 2018 at 05:48:51
I should have named the tables from the beginning, this is a good practice but I always forget and just start hammering away at the project. I will see if I can incorporate, thanks :-)

Reply ↓  Report •

#7
July 4, 2018 at 08:14:28
OK, I worked with some *real* Excel experts and they came up with a solution. I haven't used Tables and Structured References too much, so I was struggling with coming up with a way to reference a specific Row in the Table based on the value in a cell (e.g. Size).

The following example is a simplified version of your data set and the solution will need to be modified to to fit your specifics. I'm not sure how much you know about using Tables and Structured References, so you may need to do some research, just like I did. The main gist of all this (as far as I understand it) is that Excel Tables are based around a Header row, Data Columns (with names) and a Total row (if one exists). Referencing Columns is easy because they have names, as do the Header [#Headers] and Total [#Totals] rows. To reference a specific row in the Table, you use an OFFSET from the Header row.

Note: The table in I54:L57 is not just a Named Range. It is created using the Table feature under Insert...Table. When a Table is created in that manner, then Structured References can be used because Excel knows everything it needs to know about the Table. Use Google to learn more about Tables and Structured References.

Here is the simplified Table:

        I         J        K          L
11     Size     Value
12      2        6.5

...

54     Size      VA        VB         VC
55      1        7.3       6.2        5.1
56      2        8.3       7.2        6.1
57      3        9.3       8.2        7.1 

And here is the solution:

=INDEX(Table1[[VA]:[VC]],MATCH(I12,Table1[Size],0),MATCH(J12,OFFSET(Table1[[#Headers],[VA]:[VC]],MATCH(I12,Table1[Size],0),0),-1))


message edited by DerbyDad03


Reply ↓  Report •

#8
July 6, 2018 at 05:18:37
Seems complicated but I will begin my reading and youtubing :-)

Reply ↓  Report •

#9
July 6, 2018 at 06:12:24
Actually, I think the introduction of Tables and Structured References was a pretty cool idea. Without the use of the OFFSET and #Header rows, we would be forced to use Array formulas or hardcoding (like we are now.)

If you break down the INDEX function that I posted, it is really nothing more than the basic INDEX(MATCH...) formula that we are familiar with. The main difference is how you reference the Array (by table name instead of a cell range), the Columns (by name instead of number) and the Rows (by OFFSET from the #Header row instead of numbers).

It really is more flexible since you can add columns and rows (within the table or anywhere else) or move the Table around, etc. without breaking the formulas that reference the data.

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


Reply ↓  Report •

Ask Question