1

I have a long table in excel that maps speed to a list of angles. The angles are 16-bit integers, so [-180°,180) corresponds to [-32768,32768).

    Sample worksheet 1

I'm trying to create a reverse map from this data (List of speeds for each angle).

    Sample worksheet2

In order to do this, I have to search each row to see if the angle is present in the speed of that row. I want to list all matches, so in the first column I would have the lowest matching speed, the second column the second lowest, etc.

The array formula I tried was:

=SMALL(IF(NOT(ISERROR(MATCH($AA2,OFFSET($A$3:$A$597,0,3,1,9),0))),$A$3:$A$597,""),1)

My hope was that the OFFSET function would run for every cell in range $A$3:$A$597, but apparently it just does it once. I also tried some shenanigans with concatenation and CELL("row"), but got the same result. The SMALL function is just so I can select the nth lowest value.

Ram
  • 3,092
  • 10
  • 40
  • 56
TylerKehne
  • 361
  • 2
  • 12

1 Answers1

2

Array-enter the following into cell AB2:

=IFERROR(INDEX($A$3:$A$597,SMALL(IF($AA2=$D$3:$L$597,ROW($D$3:$L$597)-MIN(ROW($D$3:$L$597))+1,""),COLUMN(A1))),"")

Then copy over as many columns as you need.

Then copy the range that currently holds these formulas down as far as you need.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40