Author: Oscar Cronquist Article last updated on November 08, 2019

This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above shows the condition in cell B9 and the formula in cell range B10:B14.

The data set is in cell range A2:E7 and the lookup column is column A. The formula returns values from multiple rows if the corresponding value in the lookup column match, one value in each cell.

Jim asks:

I downloaded the file lookup-vba3. I think I can use this to help me populate a calendar.I substituted dates for Pen, Paper, and Eraser. I then had locations substituted for $ values. Where I have a date of say, 11/27/12, I have 10 locations delivering that day.

Using the template as shown in the screenshot under "Return multiple values horizontally or vertically (VBA)".

I cannot expand past column "C" to return multiple values. I think it is in the array code but I cannot figure out how to return values past column C.

If you can help, greatly appreciated!

Thanks,
Jim

Answer:

Array formula in cell B10:

=IFERROR(INDEX($B$2:$E$7, SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1)), 1/(SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7))+1/MATCH(COLUMN($B$2:$E$7), COLUMN($B$2:$E$7)), ""), ROW(A1))-SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1)))), "")

How to create an array formula

  1. Select cell B10.
  2. Click in formula bar.
  3. Paste above array formula.
  4. Press and hold CTL + SHIFT simultaneously.
  5. Press Enter.

How to copy formula

  1. Select cell B10.
  2. Copy cell (Ctrl + c).
  3. Select cell range B11:B15.