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

Thanks:  0
Likes:  0

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

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

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!

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

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
ABCDEFGHIJ
1XVALS1XVALS2XVALS3XVALS4
2
33
44
56
610

Sheet1

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

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

4. ## Re: Find Index Value of 2nd, 3rd, ect. Mentioned Value

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.

5. ## Re: Find Index Value of 2nd, 3rd, ect. Mentioned Value

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.

6. ## Re: Find Index Value of 2nd, 3rd, ect. Mentioned Value

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

7. ## Re: Find Index Value of 2nd, 3rd, ect. Mentioned Value

It works now! Thanks

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•