# Index match offset to get subsequent index values in a column

1. ## Index match offset to get subsequent index values in a column

Hi,
This is driving me nuts! I would like to pull the first 5-8 index values from a column based on random position of names. The values are randomly spaced including two in a row sometimes.

Column A:
Name1
Name1
Name1
Name3
Name2
Name1
Name2
Name3
Name3
Name2
Name2
Name1
Name1
Name2
Name2
Name1
Name1
Name1
Name3
Name2
Name1
Name2
Name3
Name3
Name2
Name2
Name1
Name1
Name2
Name2
Name1
Name1

I would like to get the index values for Name1, Name2, Name3. The index values for Name1 should be 1,2,3,6,12,13,17,16,18,21,27,28,31,32. The index values for Name2 should be 5,7,10,11,14,15,20,22,25,26,29,30. The index values for Name 3 should be 4,8,9,19,23,24.

I have tried the following column B formula: =MATCH("Name1",A:A,0) for the first row (B1) and =IFERROR(MATCH("Name1",OFFSET(\$A\$1:\$A\$20,MATCH("Name2",A:A,0)+B1-1,0),0)+B1+1,"No Data") for the subsequent rows (B2, B3, B4) where the B1 is stepped to B2 in cell B3 and so on..

I get whacky answers depending on the position of the found name. I can not get the series right for all instances.

Andrew_Step

2. ## Re: Index match offset to get subsequent index values in a column

To clarify my situation, I have attached a file to show ideal and my results.

Thank you for your help!

Andrew_Step

3. ## Re: Index match offset to get subsequent index values in a column

In other words, you want the RELATIVE positions of the name within the range?

If so, try this...

Data Range
 A B C D 1 Names Name1 Positions 2 Name1 1 3 Name1 2 4 Name1 3 5 Name3 6 6 Name2 12 7 Name1 13 8 Name2 9 Name3 10 Name3 11 Name2 12 Name2 13 Name1 14 Name1 15 Name2

This array formula** entered in D2:

=IFERROR(SMALL(IF(A\$2:A\$15=C\$1,ROW(A\$2:A\$15)),ROWS(D\$2:D2))-ROW(A\$2)+1,"")

** 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.

4. ## Re: Index match offset to get subsequent index values in a column

Hi,

Try this array formula and copy down as required:

=SMALL(IF(\$A\$1:\$A\$32="Name1",ROW(\$A\$1:\$A\$32)-MIN(ROW(\$A\$1:\$A\$32))+1),ROWS(\$1:1))

Regards

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1