Positions

[alliswell]

I have names in column A, in H1 is the name i want to find in column A for example : in H1 is John i want to list all john with their position number in Column A

Thanks

 

[Andrew Fergus]

Is using the Autofilter feature an option? It will depend on what you are trying to achieve...

 

[Peter_SSs]

Could we have some small sample data and the expected results with XL2BB?

 

[awoohaw]

I'm not sure when Control Shift Enter formulas became available in Excel. I know it was in the 2010 version.

Subsequent edited: SEARCH was not in 2007, this has been updated with FIND and UPPER case function.

Book1
	A						G	H	I
1								John	WILson
2	George Johnson							1	2
3	Sally Wilson							3	4
4	George Johnson							12	6
5	Sally Wilson							14	13
6	Sally Jones							16
7	George Wilson							17
8	George Smith							18
9	George Jackson							19
10	George Smith							20
11	Sally Jackson							21
12	George Jones							23
13	John Jackson							24
14	George Wilson
15	George Johnson
16	George Smith
17	John Johnson
18	John Jackson
19	Sally Johnson
20	George Johnson
21	John Smith
22	John Smith
23	George Jones
24	John Jackson
25	Sally Johnson
Sheet3

Cell Formulas
Range	Formula
H2:H25	H2	=IF((ROW()-1)>SUM(--(ISNUMBER(FIND(UPPER($H$1),UPPER($A$2:$A$25))))),"", SMALL(IFERROR((ROW($A$2:$A$25)-1)*(ROW($A$2:$A$25)-1)/(ISNUMBER(FIND(UPPER($H$1),UPPER($A$2:$A$25)))*(ROW($A$2:$A$25)-1)),2^100), ROW()-1))
I2:I25	I2	=IF((ROW()-1)>SUM(--(ISNUMBER(FIND(UPPER($I$1),UPPER($A$2:$A$25))))),"", SMALL(IFERROR((ROW($A$2:$A$25)-1)*(ROW($A$2:$A$25)-1)/(ISNUMBER(FIND(UPPER($I$1),UPPER($A$2:$A$25)))*(ROW($A$2:$A$25)-1)),2^100), ROW()-1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MARK858]

Search came in as a function in Excel 2000 (I was using it with 2003)

 

[awoohaw]

@MARK858 Please excuse my ignorance.
But, I went by what the help in MS-Excel Function Builder tells me:
when I clicked the "help on this function" link in this:

I get this:
I don't see 2010 or earlier versions listed

 

[MARK858]

Microsoft tend when they do an update of their support notes to only list the versions they are currently supporting, best to look at somewhere like Techonthenet, who list all the versions when they update.

MS Excel: How to use the SEARCH Function (WS)

This Excel tutorial explains how to use the Excel SEARCH function with syntax and examples. The Microsoft Excel SEARCH function returns the location of a substring in a string. The search is NOT case-sensitive.

www.techonthenet.com

 

[awoohaw]

Microsoft tend when they do an update of their support notes to only list the versions they are currently supporting, best to look at somewhere like Techonthenet, who list all the versions when they update.

MS Excel: How to use the SEARCH Function (WS)

This Excel tutorial explains how to use the Excel SEARCH function with syntax and examples. The Microsoft Excel SEARCH function returns the location of a substring in a string. The search is NOT case-sensitive.

www.techonthenet.com

i'm using excel 365. So, not sure what you mean. And i've seen notes on many functions going back to 2003 and 2007.

 

[MARK858]

If you click the link it will take you to Techonthenet's description of the Search function which lists all the main versions of Excel the Search function was available in.

Microsoft only supports versions of standalone Excel for a certain time period, the same as they do with versions of Windows

 

[Peter_SSs]

So, not sure what you mean.

Well, if you follow the link & look at the 'Applies to' you will see that SEARCH has been around since at least Excel 2000