# Search in Excel

#### Vishaal

##### Active Member
Hi,

May i search the following value/text

13452
3009
12744

in given sheet

Excel 2010 32 bit
A
B
1
2
10089​
3
3363​
4
13452​
5
3009​
6
12744​
7
12744​
8
56000​
9
56000​
10
66080​
11
56000​
12
10620​
13
17700​
14
10620​
15
17700​
16
10620​
17
5310​
18
7080​
19
7080​
20
5310​
21
10620​
22
3363​
23
13452​
24
13452​
25
3009​
26
12744​
27
56000​
28
56000​
29
66080​
30
56000​
31
10620​
32
17700​
33
10620​
34
17700​
35
10620​
36
5310​
37
7080​
38
7080​
39
5310​
40
10620​
 Sheet: Sheet1

pls help

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### tyija1995

##### Well-known Member
Hi,

In col B you can try:

B2:
=--ISNUMBER(SEARCH(A2,"13452.3009.12744"))

OR if you put your list of numbers you want to search in to a named range "list" then:
=COUNTIF(list,A2)

Hi tyija1995 Ji

its working

thanks​

Last edited:

#### Vishaal

##### Active Member
There is one problem in code

Its searching the any one amount which i have given

But i need the same

13452
3009
12744

Not any of them, Sequence also

#### Vishaal

##### Active Member

I mean i want the same sequences (whole amount )

13452
3009
12744

Last edited:

#### salim hasan

##### Board Regular
try this array formula
Code:
``=IF(ROWS(\$A\$1:A1)>COUNT(\$C\$1:\$C\$3),"",INDEX(\$A\$1:\$A\$11,SMALL(IF(COUNTIF(\$C\$1:\$C\$3,\$A\$1:\$A\$11),ROW(\$A\$1:\$A\$11)-ROW(\$A\$1)+1),ROWS(\$A\$1:A1))))``
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### Vishaal

##### Active Member

No,

In your sheet you are searching the three number (anyone of them)

but i want this group, sequence

ok let try to understand by text not value

in this sheet

Excel 2010 32 bit
A
B
1
2
Rozi
3
John
4
Lakhn
5
Peter
6
Javed
7
Rozi
8
Salim
9
Javed
10
Rajan
11
Salim
12
Javed
13
Rozi
14
Salim
15
Javed
16
 Sheet: Sheet1

I am searching
Excel 2010 32 bit
A
7
Rozi
8
Salim
9
Javed
 Sheet: Sheet1

In above sheet i want to check when Rozi, Salim and Javed come together, answer is only two times they come together and i have highlighted that cells when they come together

Excel 2010 32 bit
A
B
C
D
1
2
Rozi
3
John
4
Lakhn
5
Peter
6
Javed
7
Rozi
After Five Days​
One Time​
8
Salim
9
Javed
10
Rajan
11
Salim
12
Javed
13
Rozi
After Four Days​
Second Time​
14
Salim
15
Javed
16
 Sheet: Sheet1

#### tyija1995

##### Well-known Member

B2:
=IF(SUMPRODUCT(--({"Rozi";"Salim";"Javed"}=A2:A4))=3,"FLAG","")

Then drag it down

 A B 1 2 Rozi 3 John 4 Lakhn 5 Peter 6 Javed 7 Rozi FLAG 8 Salim 9 Javed 10 Rajan 11 Salim 12 Javed 13 Rozi FLAG 14 Salim 15 Javed

<tbody>
</tbody>

Last edited:

#### Vishaal

##### Active Member
Thanks tyija1995 Ji,

Its working fine for me

Only one thing missing, After how many days/rows

Last edited:

#### salim hasan

##### Board Regular
Code:
``=IF(OR(COLUMNS(\$A\$1:A1)>COUNTIF(\$A\$2:\$A\$13,\$D2),\$D2=""),"",INDEX(\$B\$2:\$B\$13,SMALL(IF(COUNTIF(\$D2,\$A\$2:\$A\$13),ROW(\$B\$2:\$B\$13)-ROW(\$B\$2)+1),COLUMNS(\$A\$1:A1))))``
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Last edited:

Replies
3
Views
46
Replies
0
Views
35
Replies
10
Views
102
Replies
9
Views
72
Replies
10
Views
93