search certain part of cell

[Jewelzzz00]

http://lh3.ggpht.com/_caK9XMuXnSw/S2m3s9teApI/AAAAAAAACtI/XIU7LTqAzdg/s800/SCREENSHOT.JPG

<a href="http://picasaweb.google.com/lh/photo/A8A7SWGnHkpqFZaUnjxIXw?authkey=Gv1sRgCJbSs4a3h7PnRw&feat=embedwebsite"><img src="http://lh3.ggpht.com/_caK9XMuXnSw/S2m3s9teApI/AAAAAAAACtI/XIU7LTqAzdg/s800/SCREENSHOT.JPG" /></a>

i have a array table

all the rows of data get a unique file number , no duplicates.

the file number format is 1.01, or 241.00, or 5132.03,etc.

all numbers to the left of the period symbol are "file info" reference
all numbers to the right of the period symbol are "bills" found inside the file #.
there can be any amount of file #'s, always starting from 1 thru as many as there can be (apprx. thousands of files). and the bills can be from .00 thru .99

my search field ($r$9), need to search or find only the exact match to the left of the period.

NEED to search for file 1, i need the array table to kick back only the file numbers 1.01 and 1.65, not 241.00 not 3.01.

how can i search only the text before the period to the left and exactly what is entered into my search box??

please help.

 

[Andrew Poulsom]

I don't know how you are searching, but you can get the number before the period with:

=INT(B12)

and the number after the period with:

=MOD(B12,1)

 

[Jewelzzz00]

{=IFERROR(INDEX(FILENUM,SMALL(IF(ISNUMBER(SEARCH($R$9,FILENUM)),ROW(DATA)-MIN(ROW(DATA))+1,""),ROW(DATA)-MIN(ROW(DATA))+1),COLUMN(DATA)-MIN(COLUMN(DATA))+1),"")}

this is my search, located in the above ,

IF(ISNUMBER(SEARCH($R$9,FILENUM)


? add the INT()

{=IFERROR(INDEX(FILENUM,SMALL(IF(ISNUMBER(SEARCH(INT($R$9),FILENUM)),ROW(DATA)-MIN(ROW(DATA))+1,""),ROW(DATA)-MIN(ROW(DATA))+1),COLUMN(DATA)-MIN(COLUMN(DATA))+1),"")}


says too few arguements

thank you so much,

 

[Andrew Poulsom]

R9 is already an integer. What's FILENUM?

 

[Jewelzzz00]

I don't know how you are searching, but you can get the number before the period with:

=INT(B12)

and the number after the period with:

=MOD(B12,1)

ok i got the formula to validate,

=IFERROR(INDEX(FILENUM,SMALL(IF(ISNUMBER(SEARCH($R$9,INT(FILENUM))),ROW(DATA)-MIN(ROW(DATA))+1,""),ROW(DATA)-MIN(ROW(DATA))+1),COLUMN(DATA)-MIN(COLUMN(DATA))+1),"")

but 241 still, comes up in the result.

can "", or *, or any other symbols help return the value "1", exact case

 

[Jewelzzz00]

R9 is already an integer. What's FILENUM?

FILENUM ='MAIN SHEET'!$I:$I

file # column on the main entry sheet.

 

[Andrew Poulsom]

What about swapping the arguments for SEARCH?

ISNUMBER(SEARCH(INT(FILENUM),$R$9))

 

[Jewelzzz00]

What about swapping the arguments for SEARCH?

ISNUMBER(SEARCH(INT(FILENUM),$R$9))

your great!

solution

=IFERROR(INDEX(FILENUM,SMALL(IF(ISNUMBER(SEARCH(INT(FILENUM),$R$9)),ROW(DATA)-MIN(ROW(DATA))+1,""),ROW(DATA)-MIN(ROW(DATA))+1),COLUMN(DATA)-MIN(COLUMN(DATA))+1),"")

 

[Jewelzzz00]

your great!

solution

=IFERROR(INDEX(FILENUM,SMALL(IF(ISNUMBER(SEARCH(INT(FILENUM),$R$9)),ROW(DATA)-MIN(ROW(DATA))+1,""),ROW(DATA)-MIN(ROW(DATA))+1),COLUMN(DATA)-MIN(COLUMN(DATA))+1),"")

wow so close, i tested the formula.

if you search 241 , u get the result 1.01 and 1.65 and 241.00.

any ideas?

 

[Andrew Poulsom]

How about?

ISNUMBER(SEARCH("|"&INT(FILENUM)&"|","|"&$R$9&"|"))