0

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
AK88
  • 2,946
  • 2
  • 12
  • 31

1 Answers1

2

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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • This will not work when you copy it down. It should be =INDEX(A1:D1,0,MATCH($Q$1,ABS($A$1:$D$1),0)) – Marco Vos Aug 18 '17 at 13:20
  • @MarcoVos yes it will. It just needs to be entered properly see edit for explanation on how. – Scott Craner Aug 18 '17 at 13:24
  • @ScottCraner, thank you for the reply. But the issue that we are facing is we have to take the LARGEST two values in ABSOULTE terms. AKA, if I have a row with the following numbers `1.65`, `0.5`, `0.3`, `-2.65` I will have to extract `1.65` and `-2.65`. As of now these two values are bing extracted `1.65` and `2.65` and your solution works only if the second value is `-2.65`. Is there any workaround? Maybe we should modify the way we extract the largest values in the row? – AK88 Aug 21 '17 at 05:24