Using IF(ISNUMBER(SEARCH

[mfabri]

Hi Guys,

I'm using the IF(ISNUMBER(SEARCH formula set to find a especific number in a excel file

=IF(ISNUMBER(SEARCH(E3,$D$2:$D$48)), E3, "NO")

Basicaly I have an array of numbers in E and a combination of words and numbers in D I need to know if the number in E is anywhere in D

For some reason the formula works with just one ROW but not the combination of 48 ROWS

Can someone help?

Thanks a lot!

 

[mfabri]

Try
=MATCH("*"&E3&"*",$D$2:$D$48,0)

Note, this isn't an absolute row #.
It's an Index number relative to the searched range (D2:D48)
So if E3 is found in say D5, the formula would actually result in 4
Because D5 is the 4th item in D2:D48 (D2 is 1st, D3 is 2nd etc)

Where I would put the =MATCH("*"&E3&"*",$D$2:$D$48,0), after the =IF(COUNTIF($D$2:$D$48,"*"&E3&"*"),E3,"No")

 

[Jonmo1]

but how can I know where? So I can delet that ROW

Why?
What are you planning to actualy DO with that Row# once you have it?

Are you wanting to retrieve a value from an adjescent column based on where the match is found?
Try

=INDEX($A$2:$A$48,MATCH("*"&E3&"*",$D$2:$D$48,0))

This would return the value from column A from the row where E3 is found in D.

 

[mfabri]

Where I would put the =MATCH("*"&E3&"*",$D$2:$D$48,0), after the =IF(COUNTIF($D$2:$D$48,"*"&E3&"*"),E3,"No")

=IF(COUNTIF($E$2:$E$48,"*"&F2&"*"),MATCH("*"&E3&"*",$D$2:$D$48,0),"No")

Didnt work...

 

[Jonmo1]

Where I would put the =MATCH("*"&E3&"*",$D$2:$D$48,0), after the =IF(COUNTIF($D$2:$D$48,"*"&E3&"*"),E3,"No")

It's a seperate formula, with a different purpose.
It would go in a different cell from where you put the countif.

 

[mfabri]

It's a seperate formula, with a different purpose.
It would go in a different cell from where you put the countif.

Once I found that a number in E is in the $D$2:$D$48 I need to know where in D that number is, so I can delete the ROW

 

[Jonmo1]

This will tell you the row # where E3 exists in D2:D48

=MATCH("*"&E3&"*",$D$2:$D$48,0)+1

 

[mfabri]

this will tell you the row # where e3 exists in d2:d48

=match("*"&e3&"*",$d$2:$d$48,0)+1

It worked!

 

[Jonmo1]

Great.

But this seems like it's way overcomplicated for a simple task.

If you want to delete rows that contain the value from E3 in Column D
Why not just use the AutoFilter, and use the "Contains" Text filter
And put in the value of E33.

Then you can just delete visible rows.

 

[mfabri]

Because E goes all the way to 6723

I have 6723 sets of numbers to find in 48 sets of numbers and letters. Next week will be 9000 sets of number to 2000 sets of letters and numbers...

 

[admiral100]

and my long formula
=SUMPRODUCT((--(ISNUMBER(SEARCH(E3,$D$2:$D$48)))*ROW($D$2:$D$48)))