Look up a value in an array and return multiple corresponding values by filling across columns, in Excel

In Software Engineering, Snippet

A variation on the Microsoft Excel how-to #HA001226038.

In the how-to, multiple values (column B in their example) can be returned from a single-criteria lookup, using the array formula

=INDEX($A$1:$B$7,
       SMALL(IF($A$1:$A$7=$A$10,
                ROW($A$1:$A$7)
               ),
             ROW(1:1)
            ),
       2)

This formula can be dragged (filled) vertically. However, in many instances, a horizontal fill is desired. This can be achieved with

=INDEX($A$1:$B$7,
       SMALL(IF($A$1:$A$7=$A$10,
                ROW($A$1:$A$7)
               ),
             COLUMN(A:A)
            ),
       2)

The only change required is the reference that you want to changed, e.g. from ROW(1:1) to COLUMN(A:A).

Leave a Reply