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 screen shot under "Retun 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
  5. Press Enter

How to copy formula

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

Explaining array formula in cell B10

The index function returns a value or a reference of the cell at the intersection of a particular column and row, in a given range.

INDEX($B$2:$E$7, row_num, column_num)

The first following three steps calculate the row_nums and the remaining steps calculate column_nums.

Calculate row_nums

Step 1 - Find matching dates and non blanks

($A$2:$A$7=$B$9)*($B$2:$E$7<>"")

becomes

({40909; 40910; 40911; 40912; 40913; 40909}=40909)*({"New York","Los Angeles",0,"Chicago"; 0,"Houston",0,0; "Philadelphia","Phoenix","San Antonio",0; "San Diego",0,"Dallas","San Jose"; "Jacksonville",0,0,"Indianapolis"; 0,"Austin",0,"San Francisco"}<>"")

and returns

{1, 1, 0, 1;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 1, 0, 1}

Step 2 - Return corresponding row numbers

IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), "")

becomes

IF({1, 1, 0, 1;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 1, 0, 1}, {1;2;3;4;5;6}, "")

and returns

{1, 1, "", 1;"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", "";"", 6, "", 6}

Step 3 - Return the k-th smallest value

SMALL(array, k)

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))

becomes

SMALL({1, 1, "", 1;"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", "";"", 6, "", 6}, 1)

and returns 1.

Calculate column_nums

Step 1 - Find matching dates and non blanks

($A$2:$A$7=$B$9)*($B$2:$E$7<>"")

becomes

({40909; 40910; 40911; 40912; 40913; 40909}=40909)*({"New York","Los Angeles",0,"Chicago"; 0,"Houston",0,0; "Philadelphia","Phoenix","San Antonio",0; "San Diego",0,"Dallas","San Jose"; "Jacksonville",0,0,"Indianapolis"; 0,"Austin",0,"San Francisco"}<>"")

and returns

{1, 1, 0, 1;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 1, 0, 1}

Step 2 - Calculate both row numbers and column numbers

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)), "")

becomes

IF({1, 1, 0, 1;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 1, 0, 1}, {1;2;3;4;5;6}+{1,0.5,0.333333333333333,0.25}, "")

and returns

{2,1.5,"",1.25;"","","","";"","","","";"","","","";"","","","";"",6.5,"",6.25}

Step 3 - Return the k-th smallest value

SMALL(array, k)

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))

becomes

SMALL({2,1.5,"",1.25;"","","","";"","","","";"","","","";"","","","";"",6.5,"",6.25}, 1)

and returns 1.25.

Step 4 - Subtract row numbers

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))

becomes

SMALL({2,1.5,"",1.25;"","","","";"","","","";"","","","";"","","","";"",6.5,"",6.25}, 1)-SMALL({1, 1, "", 1;"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", "";"", 6, "", 6}, ROW(A1))

becomes

1.25-1

and returns 0.25

Step 5 - Calculate column number

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)))

becomes

1/0.25 and returns column number 4.

Download excel *.xlsx file

Lookup and return multiple values from a range excluding blanks.xlsx