# Excel Formula for finding a divisable match

#### gordonfitch

##### New Member
Can anyone assist? I would like to know how to write a formula that can search a selected column for a number that is divisable by another number found in a specified cell.

I have a table with 300 rows, each numbered 1:300, I have another cell with a specific number.

I need to be able to identify each number in a specified column which is exactly divisable by the specific number.

For example: If the sum of any cell in column A, containing a number which is exactly divisable by cell (eg F6 which might contain number 11), then another action is to be applied.

My problem is that I do not know how to write a formulae that is able to compare one cells contents with a column of other cells 1:300 in an attempt to identify each number in that column which is exactly divisable by the specified number.

Thanks - Gordon Fitch

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi Gordon, welcome to the Board!

One option...
Assuming you want to scan Column A:
=MOD(A1,\$F\$6)=0

Fill down. Divisible numbers will have TRUE. You can apply a filter to isolate them.

Denis

Helo Denis, Thanks for the reply.

So I take it that by applying this formula, that all numbers in the entire column selected - in this case 'A', will be scanned to identify all numbers that are divisable by the = number nominated?

Thanks - Gordon

Why not test it yourself?

An alternative would be to use conditional formatting (Format | Conditional Formatting...) to highlight all cells in column A that meet the necessary requirement. Use the formula posted by Denis.

Helo Denis, Thanks for the reply.

So I take it that by applying this formula, that all numbers in the entire column selected - in this case 'A', will be scanned to identify all numbers that are divisable by the = number nominated?

Thanks - Gordon

gordonfitch

You indicated in your original post that if the number is exactly divisible, then another action would ensue. So, if your numbers are in column A and the test number is in F6, then I would modify Denis' suggestion to this. In B1 (copied down):
=IF(MOD(A1,\$F\$6)=0,your_other_action_here,"")

Hello Peter

Thanks for that - I have now got if working as hoped. Thanks to you and Denis.

Replies
1
Views
140
Replies
9
Views
459
Replies
0
Views
415
Replies
10
Views
390
Replies
7
Views
298

1,219,807
Messages
6,150,350
Members
450,952
Latest member
Zung

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back