exclude in search

[ExcelIsMyFave]

I'm new here, so please take it easy on me
This will likely be an easy one for the gurus in here.

Say I have the following:
Cell A1: male
Cell A2: male
Cell A3: female
Cell A4: male

I want to conduct a search using the value in cell B1, and return the row numbers for which the value in B1 exists.

So my formula in C1 is:
=small(if(isnumber(search($B$1, $A$1:$A$A4)), row($A$1:$A$A4)), row(1:1))

and I make it an array by CTRL+SHIFT+enter

So in B1 I type "male" (without the quotes). From C1 to C4 the values populate as 1,2,3,4

However, I only want the rows where "male" exists, in other words, i want an exact match and I want to exclude those cells that have "female"

I'm wanting:
C1 = 1
C2 = 2
C3 = 4
C4 = blank

Is there a way to do this?


Thanks in advance.

 

[Smitty]

Welcome to the Board!

Why not just use AutoFilter?

 

[ExcelIsMyFave]

Welcome to the Board!

Why not just use AutoFilter?

Thanks Smitty.

I suppose my question may be a little more complicated. I'm trying to return values from another sheet in my workbook. I tried to simplify my example, but here is the actual formula in the cell:

=INDEX('Sheet1'!$A$1:$D$200,SMALL(IF(ISNUMBER(SEARCH($B$1,'Sheet1'!$D$1:$D$200)),ROW('Sheet1'!$D$1:$D$200)),ROW(1:1)),1)
(Ctrl+shift+enter)

So say if the value for B1 is "male", how do I exclude the ones where it is female?

 

[T. Valko]

Thanks Smitty.

I suppose my question may be a little more complicated. I'm trying to return values from another sheet in my workbook. I tried to simplify my example, but here is the actual formula in the cell:

=INDEX('Sheet1'!$A$1:$D$200,SMALL(IF(ISNUMBER(SEARCH($B$1,'Sheet1'!$D$1:$D$200)),ROW('Sheet1'!$D$1:$D$200)),ROW(1:1)),1)
(Ctrl+shift+enter)

So say if the value for B1 is "male", how do I exclude the ones where it is female?

See this...

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[ExcelIsMyFave]

See this...

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

Thanks, however I'm a bit lost.

Using that example in the link provided, consider:
A1 = male health
A2 = female health

I want to find only the rows of the cells where the value is "male health" with no characters to the left or right of it....In other words, an exact match.

I'm sorry if I was unclear before.

 

[T. Valko]

Thanks, however I'm a bit lost.

Using that example in the link provided, consider:
A1 = male health
A2 = female health

I want to find only the rows of the cells where the value is "male health" with no characters to the left or right of it....In other words, an exact match.

I'm sorry if I was unclear before.

Can you post some real sample data and tell us what result you expect?

 

[ExcelIsMyFave]

Thanks, however I'm a bit lost.

Using that example in the link provided, consider:
A1 = male health
A2 = female health

I want to find only the rows of the cells where the value is "male health" with no characters to the left or right of it....In other words, an exact match.

I'm sorry if I was unclear before.

I should add that each cell in the range that the formula is looking within contains various words, separated by commas. So that is why I want excel to look through all the characters of all the cells in the range to get me the exact match, but excluding "female health" because I only want the rows that have "male health". Is this any clearer? Thanks

 

[T. Valko]

I should add that each cell in the range that the formula is looking within contains various words, separated by commas. So that is why I want excel to look through all the characters of all the cells in the range to get me the exact match, but excluding "female health" because I only want the rows that have "male health". Is this any clearer? Thanks

Post some REAL sample data and tell us what result you expect.

If the cells contain male and/or female then you have to refine the search criteria as male can be found within female which could lead to incorrect results due to "false positives" doing just an ISNUMBER(SEARCH(...)).

 

[ExcelIsMyFave]

Post some REAL sample data and tell us what result you expect.

If the cells contain male and/or female then you have to refine the search criteria as male can be found within female which could lead to incorrect results due to "false positives" doing just an ISNUMBER(SEARCH(...)).

A1: male health
A2: female health

B1: male health

C1 formula: =SMALL(IF(ISNUMBER(SEARCH($B$1,$A$1:$A$2)),ROW($A$1:$A$2)),ROW(1:1))
CTRL SHIFT ENTER

I copy down C1 to C2

I expect:
C1: 1 (to indicate that "male health" is on row 1)
C2: blank (to indicate that there is NOT a match for "male health)

 

[T. Valko]

A1: male health
A2: female health

B1: male health

C1 formula: =SMALL(IF(ISNUMBER(SEARCH($B$1,$A$1:$A$2)),ROW($A$1:$A$2)),ROW(1:1))
CTRL SHIFT ENTER

I copy down C1 to C2

I expect:
C1: 1 (to indicate that "male health" is on row 1)
C2: blank (to indicate that there is NOT a match for "male health)

I should add that each cell in the range that the formula is looking within contains various words, separated by commas.

Hmmm...

Ok, I'll answer the question as posted...

Book1
	A	B	C	D
1	male health	male health	5	1
2	female health	_	_	5
3	female health	_	_	6
4	female health	_	_	7
5	male health	_	_	9
6	male health	_	_	_
7	male health	_	_	_
8	female health	_	_	_
9	male health	_	_	_
10	female health	_	_	_
Sheet1

Enter this formula in C1. This will return the count of records for the search value.

=COUNTIF(A:A,B1)

Enter this array formula** in D1:

=IF(ROWS(D$1:D1)>C$1,"",SMALL(IF(A$1:A$10=B$1,ROW(A$1:A$10)),ROWS(D$1:D1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.