Author: Oscar Cronquist Article last updated on January 31, 2019

Liam asks:

Hello Oscar,
What code is needed to cause cells in Columns F - I to fill with the contents of Columns C - E when a cell in Column B includes a numeric value?

Answer:

The data set above contains random characters, some of the cells in column B contains numeric values, as well.

Array formula in cell F2:

=INDEX(\$B\$2:\$E\$6, SMALL(IF(MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, \$B\$2:\$B\$6), 0), ROW(\$A\$1:\$A\$10)), MATCH(ROW(\$B\$2:\$B\$6), ROW(\$B\$2:\$B\$6)), ""), ROWS(\$A\$1:A1)), COLUMNS(\$A\$1:A1))
How to enter an array formula
1. Copy formula above
2. Doubleclick on cell F2
3. Paste formula
4. Press and hold CTRL + SHIFT
5. Press Enter

If you did this correctly, the formula in the formula bar now begins with a curly bracket and ends with a curly bracket, like this: {=formula}

Don't enter these curly brackets yourself, they will appear if you did the above steps.

Copy cell F2 and paste to cell range F2:I6.

### Explaining array formula in cell F2

Step 1 - Look for values in a cell range

SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, \$B\$2:\$B\$6)

becomes

SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, {"ab12"; "abc"; "def"; "a21b"; "cde"})

and returns this array:

{3, 4, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; 3, 2, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!}

Step 2 - Remove errors

IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, \$B\$2:\$B\$6), 0)

becomes

IFERROR({3, 4, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; 3, 2, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!}, 0)

and returns

{3, 4, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 3, 2, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}

Step 3 - Return the matrix product of two arrays

MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, \$B\$2:\$B\$6), 0), ROW(\$A\$1:\$A\$10))

becomes

MMULT({3, 4, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 3, 2, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, ROW(\$A\$1:\$A\$10))

becomes

MMULT({3, 4, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 3, 2, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {1;2;3;4;5;6;7;8;9;10})

and returns

{11;0;0;7;0}

Step 4 - Check whether a condition is met

IF(MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, \$B\$2:\$B\$6), 0), ROW(\$A\$1:\$A\$10)), MATCH(ROW(\$B\$2:\$B\$6), ROW(\$B\$2:\$B\$6)), "")

becomes

IF({11;0;0;7;0}, MATCH(ROW(\$B\$2:\$B\$6), ROW(\$B\$2:\$B\$6)), "")

becomes

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

and returns {1;"";"";4;""}

Step 5 - Return the k-th smallest value in array

SMALL(IF(MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, \$B\$2:\$B\$6), 0), ROW(\$A\$1:\$A\$10)), MATCH(ROW(\$B\$2:\$B\$6), ROW(\$B\$2:\$B\$6)), ""), ROWS(\$A\$1:A1))

becomes

SMALL({1;"";"";4;""}, ROWS(\$A\$1:A1))

becomes

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

and returns 1.

Step 6 - Return a value of the cell at the intersection of a particular row and column

=INDEX(\$B\$2:\$E\$6, SMALL(IF(MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, \$B\$2:\$B\$6), 0), ROW(\$A\$1:\$A\$10)), MATCH(ROW(\$B\$2:\$B\$6), ROW(\$B\$2:\$B\$6)), ""), ROWS(\$A\$1:A1)), COLUMNS(\$A\$1:A1))

becomes

=INDEX(\$B\$2:\$E\$6, 1, COLUMNS(\$A\$1:A1))

becomes

=INDEX(\$B\$2:\$E\$6, 1, 1)

becomes

=INDEX({"ab12", "PEN", "YPT", "KVF"; "abc", "ZLZ", "KIK", "HQX"; "def", "CJI", "YMI", "STC"; "a21b", "TQW", "XHA", "UBM"; "cde", "YZX", "GLT", "TED"}, 1, 1)

and returns ab12 in cell F2.

### Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.
* You will also get a weekly newsletter, unsubscribe anytime!

If you rather want to use an excel table filter, follow these instructions

1. Select data set, cell range B2:E6
2. Go to tab "Insert" on the ribbon
3. Click "Table" button or press CTRL + T
4. Click OK
5. Double click on cell F2
6. Type: =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},B3))>0
7. Press Enter
8. Click on black arrow on Column 5 (F)
9. Filter "True"
10. Click OK