# Combining formula results to create a dynamic index

#### Randy_22

##### New Member

In one column I am using the match function to match an item on one list to an item on another, returning the index of the matched item. Now since there are multiple occurrences of items on the second list I want to have a second column that seeks to find a match that may occur after the first match. So I want the match array in the second column to begin on the row after that which was provided in the first column. That is why I included the match function from the first column which is underlined here. this is what I came up with:

=MATCH(F2, "'Extra Parts Summary'!C"&"(MATCH(F2,'Extra Parts Summary'!C\$2:C\$299,0))+1":C299,0)

I want the result of the formula from the first column +1 to combine with the column index C using the ampersand and for the match formula to search from that point to the end of the list on the second page. But I can't get it to work. The text I highlighted red is the problem I believe. Any help is appreciated!

Thanks

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Snakehips

##### Well-known Member
Randy_22,

You will need to use the INDIRECT function if you are to build a reference in that way.
Unless I've typo'd then try....

=MATCH(F2,INDIRECT( "'Extra Parts Summary'!C"&MATCH(F2,'Extra Parts Summary'!C\$2:C\$299,0)+1&":C299"),0)

Hope that helps.

Thank You

Replies
4
Views
103
Replies
4
Views
210
Replies
2
Views
244
Replies
2
Views
12
Replies
9
Views
124

1,109,350
Messages
5,528,191
Members
409,807
Latest member
nicky736

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...