# How can I find specific decimals using array formula?

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?

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?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.

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?

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.

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? ...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?

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.

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.

(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

`=`

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