CODEWITHSUNDEEP
excelvbamatchabsolute-value
AK88
AK88

Reputation: 3026

Match absolute value and return whole column EXCEL

We have a row with indicative numbers from which two largest absolute values are extracted. Under these indicative numbers we have data spanning to n rows. Now we'd like to get the rows for each of those largest indicative values.

For example:

-1.6 2.5 0.5 1.2
gh  bh  dh  jh
12  45  45  89

The following formulas find the first two largest values (in let's say P1 and Q1):

=LARGE(ABS($A$1:$D$1),1)
=LARGE(ABS($A$1:$D$1),2)

Now the formula below worked if the largest indicatives are POSITIVE:

=INDEX(A:D,0,MATCH($Q$1,$A$1:$D$1,0))

We can't get it working for indicatives when they are NEGATIVE. Any tips would be appreciated. Thanks.

Expected output in this particular case should look like:

-1.6 2.5
gh  bh
12  45

Upvotes: 0

Views: 1068

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

use this array formula:

=INDEX(A:D,0,MATCH($Q$1,ABS($A$1:$D$1),0))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

That will return the full column as an array. If you want to display the full column. Highlight enough rows to show the data with the top row as active. Put the above formula in the formula bar and hit Ctrl-Shift-Enter.

Excel will fill the selected cells with the formula in an array and put the {} around each formula.

enter image description here

Upvotes: 2

Related Questions