How can I find specific decimals using array formula?

English
, ,
#1

On column A3:A15 I have a series of numbers with decimals. 10.2, 13.21, 45.26 and so on. I wrote the following formula:

=SMALL(IF(ISNUMBER(SEARCH(B3,A3:A15)),ROW(A3:A15),""),1)

By typing a decimal in B3, such as …21, it will give me the row number for the cell with 13.21. But if I type …20 it will find nothing. If I switch it to .0.2 it will find the row number for the cell with 10.2. But if change the formula to this:

=SMALL(IF(ISNUMBER(SEARCH(B3,A3:A15)),ROW(A3:A15),""),2)

and I type .0.2 on B3, it will give me the row number for 13.21.

What can I type on B3 so it can inly detect numbers with .2 but not .21, .22, .23, etc?

#2

Where is the array formula you are talking of in the subject?
Are the contents of A3:A15 and of B3 actually of type number or of type text?
Are you aware of the facts that
… SEARCH is working on texts and will thus need to convert numbers to text?
… such an automatic conversion will never return a result without a digit in front of the decimal separator?
… that the coversion will not regard the ‘Numbers’ format set for the source cell?
What shall I type .0.2 on B3 mean?

#3

Are you aware of the fact that SEARCH will evaluate its first parameter as a regular expression if the respective option is set? And that the point (full stop) has a special meaning in RegEx?
Using SEARCH with numbers you should explicitly convert them with the help of the TEXT function with appropriate format codes.

#4

I know SEARCH applies to text. What can I use instead of SEARCH for look for an exact number? See my response to karolus. Basically I need an array formula to find an exact value. If in D I have 1200.45, 1200.11 and 1200, what can I use to find ONLY 1200 and not 1200.45 or 1200.11?

#5

Also: I need to do this without having to reformat the contents of D column as text. That is simply not possible for the data I will be using.

#6

You should rather tell what you actually want to achieve, instead of what formula didn’t do the job.
There is no problem with converting numbers “on the fly”. You didn’t talk originally of matcehs for numbers, but for textual pieces as far as I understood. What did you mean by typing .0.2 on B3? What about the first point there? …

#7

I’m trying to find the row number in a column that matches a specific number (in this case 1200) without having the search result mixed up with 1200.45, 1200.11, etc. And I’m trying to achieve this using an array formula. All I need is something that will find the correct match of 1200. If SEARCH doesn’t work, then what?

#8

By the way: .0.2 on B3 means I was looking for any number that contained the decimal 0.2 using B3 as reference for the search.

#9

In short, I’m doing a SEARCH for NUMBERS. SEARCH doesn’t do the trick because it’s intended for text. So I need something similar to SEARCH that can be used for numbers.

#10

(I still do not understand the first point . .)
“.0.2 on B3 means I was looking for any number that contained the decimal 0.2 using B3 as reference for the search.”
To me this again means that you are searching for specific sequences of characters (decimal separator, decimal digits). This is seraching for text. SEARCH or FIND may be the appropriate functions depending on additional info.
Searching for exact numbers would use simply a comparison by =.

#11

The first point is a wildcard. When you add a dot to a number or to text when doing a search, it means you are looking for any content that has that number or text. Example: if you have 34.31 in A1 and ext.31 in A2, if you search for …31 you will get A1 and A2 as a correct answer. The . is like the asterisk. It’s a wildcard.

#12

If I use SEARCH for .0.2 the formula will return any number from .20 to .29. I’m trying to narrow it to just .20. I will try with FIND, but I doubt the result will be different, as FIND applies to uppercase and lowercase.

#13

If you are using regular expressions with SEARCH (FIND doesn’t evaluate them), the first point stands for ‘any charcater’. So does the second one if not escaped by \.
“When you add a dot to a number or to text when doing a search, it means …” Something to be used as a RegEx must always be text. Your usage of the term “number” is not adequate in the context of spreadsheets.

#14

“… but I doubt the result will be different, as FIND applies to uppercase and lowercase.”
As long as we are talking of the texts used to display numbers in decimal notation (without the scaling extension used for the ‘Scientific’ variant) upper case or lower case do not exist.

#15

B3 → 0.2

=SMALL(IF(ABS(MOD(A$3:A$15,1)-B$3)<0.0000000001,ROW(A$3:A$15),""),ROW(A1))

enter with ctrl+shift+enter and hold ctrl-key meanwhile pulling down.

#16

Didn’t work. What I need is a formula to find the correct number in an array. Let’s say that in column D I have 1200.45, 1200.11, and 1200, and I only want to find out the row number of the cell with 1200. I need to do this using an array formula:

=SMALL(IF(ISNUMBER(SEARCH(1200,$D$2:$D$302)),ROW($D$2:$D$302),""),1)

The problem with that formula is it will return as a match ANY number with 1200 (1200.45, 1200.11, etc), not just 1200. I understand SERCH applies to text. What can I use instead?

#17

SOLVED AT LAST!

{=SMALL(IF(TRUNC($D$2:$D$100)+$B$1=$D$2:$D$100,ROW($D$2:$D$100),""),1)}

In which B1 contains the decimal to find in the array. The result of the formula is the row number of the cell containing any number with the decimal .30, but not .31, .32, .33, etc.

Prior to finding the solution for decimals I found a solution for finding whole numbers:

After much struggle I figured out a formula to find whole numbers.

{=SMALL(IF($D$2:$D$100=$AG$2,ROW($D$2:$D$100),""),1)}

In which D2:D100 is the column to be searched, and AG2 contains the value to look for. In this case the whole number 1200.

By repeating the formula as an array, and changing the 1 at the end for 2, 3, 4, etc, I find the next row number containing the number 1200. This formula will not confuse 1200 with 1200.45, 1200.11 etc.

Now I have to figure out a similar formula to find the row number of cells that contain decimal numbers such as 0.1, 0.2, 0.3, etc. For example, find the row number if a cell contains the number 1200.20 but not the number 1200.21. (UPDATE: I solved it. See edit and formula above.)

So if you are still interested in helping me out with this, I would really appreciate it. (UPDATE: If you have a different solution to the one I found I’d like to see it too.)

#18

Oops! I forgot to add the curly brackets to the formula. The formula should be:

{=SMALL(IF($D$2:$D$100=$AG$2,ROW($D$2:$D$100),""),1)}

The brackets, of course, are added by hitting CTRL-SHIFT-ENTER when entering the array formula.

Again, this formula will find whole numbers, not numbers with decimals using a wildcard for the decimal. See my original question to understand what I mean by this.

#19

To find any text occurring as a part of the text resulting from default conversion of numbers, you can always use the same formula with FIND. If relying on the default conversion is not working how you want/expect you have to use the TEXT function explicitly providing a format code.
If you are searching an array for a numeric value, stick to the comparison by algebraic comparators like = or >=.

#20

Sounds reasonable. I found a solution using a math formula adding the specific decimal to TRUNC. See the solution in my updated response.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.