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.
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.
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.
“… 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.
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.
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?
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.)
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.
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 >=.
Sounds reasonable. I found a solution using a math formula adding the specific decimal to TRUNC. See the solution in my updated response.
You ( @vh ) may study the attached example.
(Editing with respect to the comments by @vh 
I am still confused about this somehow, but I lack the time to read every comment on every contribution here again.
If the solution you give factually is what you wanted, it was about the fractional part of the numbers in A3:A15. Since the internal handling of numbers is not decimal I wouldn’t talk of decimals in the case and surely not specialize on the point as the decimal separator.
In principle the appropriate expression returning the fractional part as you would see it displayed would be: MOD(ABS($A$3:$A$15);1). However, a comparison for equality with $B$3 would suffer from the effect of numerical extinction. Comparison for equality is always somehow problemtic in machine arithmetc.
Your formula will work better with this respect, but it will not return the result you may expect if A3:A15 can contain negative numbers.
Never trust in a formula just based on a few tests. There may be unexpected effects of signs, scale factors (order of magnitude) , and whatever in machine arithmetic because of its dyadic working and its limited precision.
To make your formula also work with negative numbers it should read:
{=SMALL(IF(IF($A$3:$A$15>=0;1;-1)*(TRUNC(ABS($A$3:$A$15))+$B$3)=$A$3:$A$15;ROW($A$3:$A$15);"");1)}.
To make it return all the matching rows at once yo migh use
{=IFERROR(SMALL(IF(IF($A$3:$A$15>=0;1;-1)*(TRUNC(ABS($A$3:$A$15))+$B$3)=$A$3:$A$15;ROW($A$3:$A$15);"");ROW($A$3:$A$15)-ROW($A$3)+1);"")}
(There are disadvantages of array output, however.)
On the other hand you once again stressed “I needed a solution WITHOUT having to convert anything to text.” I still assume a misunderstanding. A solution based on internal conversion (during formula evaluation) might even simplify a specific task without any effects concerning the usage of the sheet. The formula allowing for negative numbers I gave may not be very clear with respect to the intentions. Where interested in decimal digits whether left or right of the decimal separator, you are actually interested in text. The formula might better not veil this fact.
In addition working with helper columns (that may get hidden) will often allow for a better and much clearer structure in solutions. You should consider it. In this case the preparation of the relevant parts of numbers might be done in helper columns to simplify and make readable the formulae. Lack of a proper way of design-documentation is one of the most relevant disadvantages of spreadsheets. Helper columns can also help to relieve this.
I understand your logic, but it would be impossible for me change the numbers in the array to text without causing problems elsewhere in the spreadsheet. In the actual spreadsheet the numbers in column D are constantly being updated and there’s no time to change them to text. That is why I need to figure out a formula to find numbers with specific decimals without having to rely on changing the numbers to text manually.
Let’s stick to the original range of A3:A15 you gave (I created a copy below).
The content of this range in my example is numbers, not text. These numbers are converted automatically on the fly when needed for the evaluation of the formulae. B3 (and B20 respectively) are the cells that must contain text. Otherwise you would have to accept useless complications.
I understand. But I needed a solution WITHOUT having to convert anything to text. And I found it (using the original columns and rows):
{=SMALL(IF(TRUNC($A$3:$A$15)+$B$3=$A$3:$A$15,ROW($A$3:$A$15),""),1)}
That did the trick. If I enter 0.1, 0.2, 0.3, etc in B3, the formula will give the row number of ONLY the cells with the specific decimal. That way if A3 has 100.30, A4 has 100.32 and A5 has 100.35, the formula will only pick A3 for the row number and it will not confuse it with .32 etc.
Your comments were of great help, however. In order to find this solution I followed your tip of using an algebraic evaluation to find the decimals instead of using SEARCH, which is intended for text. So I do appreciate your comments. Thanks!
Oh, and by the way; for those who don’t know, TRUNC removes the decimals from a number. Thus, the following formula:
=TRUNC(500.34)
Will return 500 as a whole number. Likewise, =TRUNC(B3) , in which B3 is 500.34 will generate the same result.
So if I add the decimal I’m looking for to a TRUNC number, the result should be the number with the decimal I’m looking for. By evaluating the number with math it solves the problem of finding specific decimals without confusing them with .31, etc.