See excel sheet.

Given the following Data Table, inputs of "Method 11" and "Value: 500" should output the "mm2" result of 185: (Since 533 is 33 away from 500, while 464 is 36 away from 500)

See excel sheet.

Given the following Data Table, inputs of "Method 11" and "Value: 500" should output the "mm2" result of 185: (Since 533 is 33 away from 500, while 464 is 36 away from 500)

I am trying to find the highest closest value in this table, selected "method 11" in cell G2, and the value is 500. The answer returned from the table should be cell A16 - 185.

I am using excel 2019 and I tried index&match;, vlookup; and lookup&FREQUENCY, but all failed.

• excel
• excel-formula
• excel-2019

• 1 Have you tried XLOOKUP? Also please edit your question with the tried functions, even if it not produced the desired result, and the reason why it not fulfill the requirements. – Black cat Commented Mar 31 at 13:45
• 1 Please share the formulas you have tried and how they failed. Also, share more results to clarify what "the highest closest value" actually means, i.e., the results for numbers 499, 501, and 544-546. – VBasic2008 Commented Mar 31 at 14:19
• Are the rows of the table always sorted in ascending order? For all columns? – Chronocidal Commented Mar 31 at 14:28
• 1 @Blackcat Are you sure XLOOKUP exists in Excel 2019? I fixed my answer because of that. – user80346 Commented Mar 31 at 14:48

4 Answers 4

There are a few ways. Using INDEX and MATCH functions with MIN and ABS, or just using XLOOKUP can solve the problem you are having

Using INDEX:

=INDEX(A2:A20, MATCH(MIN(ABS(D2:D20-G2)), ABS(D2:D20-G2), 0))

Assuming you are new to these, ABS(D2:D20 - 500) calculates the absolute difference between the values in column B and five hundred. MIN(ABS(D2:D20 - 500)) finds the smallest difference, which corresponds to the closest value.

MATCH(MIN(...), ABS(D2:D20 - 500), 0) finds the position of that smallest difference in the range. INDEX(A2:A20, ...) returns the value in column A that corresponds to that position.

You can also find some answers here for more information on how to find the closest value in tables.

As you are using the 2019 version of Excel, try the following:

=INDEX(A2:A21,AGGREGATE(15,6,(ROW(A2:A21)-ROW(A1))/(B2:D21>G3)/(B1:D1=G2),1))

If the exact match should be chosen as in the case of cell C16 in method 1 then modify the formula to read:

=INDEX(A2:A21,AGGREGATE(15,6,(ROW(A2:A21)-ROW(A1))/(B2:D21>=G3)/(B1:D1=G2),1))

You can identify the column to search in with the MATCH function:

MATCH($G$2, $B$1:$D$1, 0)

You can return the full Column by using INDEX, which will return the entire Row or Column if the Column or Row is explicitly omitted:

INDEX($B$2:$D$21,, MATCH($G$2, $B$1:$D$1, 0))

Then you can calculate the ABSolute Difference between the values in this column, and your target value:

ABS(INDEX($B$2:$D$21,, MATCH($G$2, $B$1:$D$1, 0))-$G$3)

(For brevity, I will be ellipsing much of that formula from this point)

So, the 'closest' value will be the one with the MINimum Absolute Difference. You can then use MATCH to find its row:

MATCH(MIN(ABS(…)), ABS(…), 0)

And then return the corresponding value from Column A using INDEX:

=INDEX($A$2:$A$21, MATCH(MIN(ABS(…)), ABS(…), 0))

You also can make the Ranges Dynamic in length, by taking advantage of the fact that INDEX is treated as a normal cell reference:

$B$2:INDEX($D:$D, COUNTA($A:$A))
$A$2:INDEX($A:$A, COUNTA($A:$A))

or even expand this to be Dynamic in width too:

INDEX($B:$B, COUNTA($A:$A)):INDEX($2:$2, COUNTA($1:$1))
$B$1:INDEX($1:$1, COUNTA($1:$1))

Of course, this will make your formula longer and more difficult to read…

(N.B. in some versions of Excel, you will need to press Ctrl+Shift+Enter to enter this as an Array Formula)

Here is an alternative working solution you could try to accomplish the desired output:

=INDEX(A2:A21,
MATCH(AGGREGATE(15,6,ABS(G3-INDEX(B2:D21,,MATCH(G2,B1:D1,0))),1),
ABS(G3-INDEX(B2:D21,,MATCH(G2,B1:D1,0))),0))

• Using the MATCH() function to get the desired position of the column or the method

• Encapsulate the above within the INDEX() function and placing the MATCH() for the [column_num] to get the entire column.

• Get the absolute difference between G3 and the extracted values from above.

• Using AGGREGATE() function get the smallest value in the range by ignoring errors to returns the row position of closest value to G3

• Now, using MATCH() again find the row position of the absolute difference matches the smallest absolute difference found in the last step.

• Finally, encapsulate the last within INDEX() to return the corresponding value.

If applicable using LET()

=LET(
     a, B2:D21,
     b, B1:D1,
     c, INDEX(a,,MATCH(G2,b,0)),
     d, ABS(G3-c),
     INDEX(A2:A21,MATCH(AGGREGATE(15,6,d,1),d,0)))