Find Index Value of 2nd, 3rd, ect. Mentioned Value

[jfulks13]

Hello - I have data in columns A1:PQ1 and I need to find the position of several different letter/number combinations in the array. For example, when I use:

=MATCH("XVALS"&"*",$A$1:$PQ$1,0)

it returns 33 (corresponding to AG1). For the next search I can change $A$1 to $AE$1, but this complicates everything because the indexing does not match the original array. Any help?

Thanks!

 

[StephenCrump]

Welcome to the Forum!

C3 (array-entered): =SMALL(IF(ISNUMBER(SEARCH("XVALS",A$1:PQ$1)),COLUMN(A$1:PQ$1)-COLUMN(A$1)+1),ROWS(C$3:C3))

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1			XVALS1	XVALS2		XVALS3				XVALS4
2
3			3
4			4
5			6
6			10
Sheet1

 

[jfulks13]

StephenCrump - Thank you for your reply. When I try this, it returns #NUM !. I think it is because the "XVALS" I am looking for contains text. For examples cell AG1 and FV1 on my spreadsheet contain
"xvals:[0.005" and "xvals:[0.0053"
Even though these are distinct, an issue arises when I run into the second "xvals:[0.005"

Thanks

 

[StephenCrump]

The formula in C3 needs to be array-entered, i.e. type the formula and hit the CTRL-SHIFT-ENTER keys together, rather than just ENTER.

Copy this formula down the column and you shouldn't get #NUM errors until you run out of found values.

You can wrap the formula in an IFERROR( ... , "") to suppress these errors.

 

[jfulks13]

Still get #NUM error. I'm using Excel 2013 if that makes a difference. I entered it exactly as you did above, no luck. Also, I would like to avoid using array-enter because I will need to copy similar data into this template and I think it gives me issues when I try and change an array that is being referenced.

 

[StephenCrump]

I'm not sure why yours isn't working, but here's my file for comparison: https://app.box.com/s/dr79pmur4jtb54i3b9w7cedasx5y5een

 

[jfulks13]

It works now! Thanks