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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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

V
  • Question Question
Searching for text in textboxes in Excel
2
Replies
15
Views
389
DanteAmor
DanteAmor
B
  • Question Question
Copy, paste based on excel sheet name
Replies
1
Views
219
eiloken
E
Dan Wilson
  • Question Question
Options help
Replies
0
Views
97
Dan Wilson
Dan Wilson
P
  • Solved
FORMULA FOR CATEGORIES
2
Replies
10
Views
439
punit83
P
D
  • Solved
Exact Match with ISNUMBER(SEARCH)
Replies
2
Views
185
danhenshy23
D

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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