Wildcard Search Criteria

Having a problem getting the formula in column 'C' to function with a wildcard.

I want to display the data if the name in the drop down box matches any name in column 'D'.

Jim O

In C2 copied down,

=IF(ISNUMBER(SEARCH($H$2,D2)),MAX($C$1:$C1)+1,0)

In H3 copied down

=IFERROR(INDEX($E$2:$E$128,MATCH(G3,$C$2:$C$128,0)),"")
See attached

Assuming that your list will eventually have more than one match per director enter this array formula in H3 and fill down as far as necessary.

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

I changed the formula in column C to:

Formula:

Please Login or Register  to view this content.

	C	D	E	F	G	H
1		Film & Director(s)				Director
2	1	Alfred Hitchcock	Film 1			Alfred Hitchcock
3	0	Anthony Harvey	Film 2			Film 1
4	0	Arthur Penn	Film 3			Film 6
5	0	Barry Sonnenfeld	Film 4			Film 10
6	0	Billy Wilder	Film 5
7	1	Alfred Hitchcock	Film 6
8	0	Brad Bird	Film 7
9	0	Brian G. Hutton	Film 8
10	0	Burt Lancaster	Film 9
11	1	Alfred Hitchcock	Film 10
12	0	Christopher Nolan	Film 11

Try

=COUNTIF(D$2:D2,"*"&H2&"*")

"*" doesn't work as a wildcard in your formula, it just adds a * to each end of the string in the cell.