Search in Excel

V

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
543
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi,

Thanks in advance.

May i search the following value/text

13452
3009
12744

in given sheet

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
10089​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
3363​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td=bgcolor:#FFFF00]
13452​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td=bgcolor:#FFFF00]
3009​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#FFFF00]
12744​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
12744​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
56000​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
56000​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
66080​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
56000​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
10620​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]
17700​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td]
10620​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td]
17700​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td]
10620​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td]
5310​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td]
7080​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td]
7080​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td]
5310​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td]
10620​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td]
3363​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td]
13452​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td=bgcolor:#FFFF00]
13452​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td=bgcolor:#FFFF00]
3009​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
26​
[/td][td=bgcolor:#FFFF00]
12744​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
27​
[/td][td]
56000​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
28​
[/td][td]
56000​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
29​
[/td][td]
66080​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
30​
[/td][td]
56000​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
31​
[/td][td]
10620​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
32​
[/td][td]
17700​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
33​
[/td][td]
10620​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
34​
[/td][td]
17700​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
35​
[/td][td]
10620​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
36​
[/td][td]
5310​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
37​
[/td][td]
7080​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
38​
[/td][td]
7080​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
39​
[/td][td]
5310​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
40​
[/td][td]
10620​
[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

pls help
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
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)
 
Upvote 0
Hi tyija1995 Ji

its working

thanks​
 
Last edited:
Upvote 0
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
 
Upvote 0
I mean i want the same sequences (whole amount )

13452
3009
12744
 
Last edited:
Upvote 0
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))))

Book1
ABCDEFG
1573313452sum
2673630091345229205
313452127443009
4651812744
512811
63009
710700
810646
912363
1012744
117076
Sheet1
Cell Formulas
RangeFormula
G2=SUM($E$2:$E$10)
E2{=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))))}
E3{=IF(ROWS($A$1:A2)>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:A2))))}
E4{=IF(ROWS($A$1:A3)>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:A3))))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]Rozi[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]John[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]Lakhn[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]Peter[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]Javed[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]Rozi[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]Salim[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]Javed[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]Rajan[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]Salim[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]Javed[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]Rozi[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td]Salim[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td]Javed[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

I am searching
Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]Rozi[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]Salim[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]Javed[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]Rozi[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]John[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]Lakhn[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]Peter[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]Javed[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td=bgcolor:#FFFF00]Rozi[/td][td]
After Five Days​
[/td][td]
One Time​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td=bgcolor:#FFFF00]Salim[/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td=bgcolor:#FFFF00]Javed[/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]Rajan[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]Salim[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]Javed[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td=bgcolor:#FFFF00]Rozi[/td][td]
After Four Days​
[/td][td]
Second Time​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td=bgcolor:#FFFF00]Salim[/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td=bgcolor:#FFFF00]Javed[/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Upvote 0
How about

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

Then drag it down

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Rozi[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]John[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Lakhn[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Peter[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Javed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Rozi[/TD]
[TD]FLAG[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Salim[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Javed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Rajan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Salim[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Javed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Rozi[/TD]
[TD]FLAG[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Salim[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Javed[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Thanks tyija1995 Ji,

Its working fine for me

Only one thing missing, After how many days/rows



 
Last edited:
Upvote 0
How About This formula
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))))

Book1
ABCDEFG
2A1 :Time AA1 :Time A2 :Time A 
3B1 :Time BB1 :Time B2 :Time B
4C1 :Time CE1 :Time E2 :Time E3 :Time E
5D1 :Time D
6E1 :Time E
7A2 :Time A
8B2 :Time B
9E2 :Time E
10D2 :Time D
11M1 :Time M
12E3 :Time E
13O1 :Time O
Sheet1
Cell Formulas
RangeFormula
B2=COUNTIF($A$2:A2,A2)&" :Time "&A2
E2{=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))))}
F2{=IF(OR(COLUMNS($A$1:B1)>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:B1))))}
G2{=IF(OR(COLUMNS($A$1:C1)>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:C1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
help with a search function
Replies
8
Views
85
Cubist
Cubist
V
  • Question Question
Searching for text in textboxes in Excel
2
Replies
15
Views
668
DanteAmor
DanteAmor
C
  • Solved
List Results from All Sheets Search
2
Replies
16
Views
275
MikeVol
MikeVol
B
  • Question Question
Copy, paste based on excel sheet name
Replies
1
Views
224
eiloken
E
R
  • Question Question
Permanent Search Box
Replies
7
Views
89
Skyybot
Skyybot

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top