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 rowHere 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,240I 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)

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

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 6Then this will be a mirror image:

A B C 4 =C1 =B1 =A1 5 =C2 =B2 =A2You 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

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

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 theTableas opposed to Rows and Columns in thespreadsheet. 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

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 :-)

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

Tablefeature 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.1And 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

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

Ask Your Question

Weekly Poll

Would you be willing to go on a SpaceX trip around the moon?

Discuss in The Lounge

Poll History