## Filter rows where a cell contains a numeric value

Liam asks:

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:**

**How to enter an array formula**

- Copy formula above
- Doubleclick on cell F2
- Paste formula
- Press and hold CTRL + SHIFT
- 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 *.xlsx file

Filter records containing a value.xlsx

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

### Category: Mmult function

Comments(13) Filed in category: Excel, MMULT function, Permutations, SUM function

Comments(7) Filed in category: Count values, Excel, MMULT function, SUM function

Lookup with multiple criteria and display multiple unique search results (array formula)

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]Comments(5) Filed in category: Excel, MMULT function, Search and return multiple values, Unique distinct values

MMULT function – Matrix multiplication

MMULT(array1, array2) Returns the matrix product of two arrays, an array as the same number of rows as array1 and […]Comments(4) Filed in category: Excel, MMULT function

Count overlapping days across multiple date ranges

NC asks: Thanks a tonne, Oscar. It took me about 8 hours to work through this formula piece by piece, […]Comments(1) Filed in category: Excel, MMULT function, Overlapping

Working with overlapping date ranges

Today's blog post is about date ranges, the techniques demonstrated here can also be applied to time or other numerical […]Comments(1) Filed in category: Excel, MMULT function, Overlapping

### 10 Responses to “Filter rows where a cell contains a numeric value”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hello Oscar

I place lots of pictures in word 2016 files. As a person reads the report, I would like a button in the line to open a picture. The picture would relate to the text that was just read. Like you did with Excel (Show / Hide). It would be great if I could change the pictures with ease. Could you write this program? What would be the cost?

John

Dear Oscar,

I Need solution to get summary of days on which the specific leaves have been taken by an employee. Suppose on Ist and 2nd day he availed two casual leaves so summary of days should be come up like 1,2(days) in same row and same applicable for Present days, Festival Holidays.

1 2 3 4 5 6 7 CL FH P

CL CL P P P NH NH 1,2 14,15,16 3,4,5,

Would be grateful to you.

Warm Regards

SACHIN

Hello Oscar, Honestly you are one of the most skilful, intelligent people in excel/vba field. You have unique way of teaching others and communicating knowledge. Simply you awesome. Thank you very much. I am regular reader of your site and I find it very useful learning resource.

Abbasabdulla,

Thank you.

dear Oscar, many thanks for your support & efforts

i need your help,

i have excel sheet as follow

AAA 18-Jan-17

AAA 19-Jan-17

AAA 20-Jan-17

AAA 18-Mar-17

AAA 22-Jan-17

AAA 23-Jan-17

BBB 24-Jan-17

BBB 25-Jan-17

BBB 18-Mar-17

BBB 27-Jan-17

BBB 28-Jan-17

BBB 29-Jan-17

BBB 30-Jan-17

CCC 31-Jan-17

CCC 1-Feb-17

CCC 2-Feb-17

CCC 18-Mar-17

CCC 4-Feb-17

CCC 5-Feb-17

column A has over than 30000 rows consist of duplicate values and in column B the date corresponding to this value.

i wish to create conditional format formula to identify the most recent date only.

in the above example i wish to highlite only the date of 18-March-17.

hope to hear from you soon

kind regards

M. Saad

Thank you, I hope this can be useful.

dear Mr. Oscar , many thanks for your prompt action & answer, apologize that my question was not clear enough for you.

the formula is working very good but i wish to check the recent date for each value in column A

i want formula to check all dates related to cell contain value A and then choose the most recent date , and then check all dated related to cell contain value B and then choose the most recent value and after that go for all dates related to cell contain value C and choose most recent date and etc...

sorry if i waste your time in first question , but actually i want to create link between column A which contain values and column B which contain dates.

sorry again and hope my question is clear this time

kind regards

M. Saad

I believe you are looking for this:

Dear Mr. Oscar

I do appreciate your effort , this exactly what I need for my file.

Again, many thanks and wish you very nice day

Kind regards

M. Saad

You are welcome.