Index matching on + rows, condition help required

[Boniouk]

Hi, I have a formula which does a lookup against an Id, and matches it in another sheet and returns a column, a bit like a vlookup apart from this gives me the option to do a +1 at the end of the formula to get multiple rows. This means i can retreive data for a required person, and pull multiple rows. However, i dont know how to put a condition that if the ID no longer matches, to stop pulling the data.

So far I have this.


=INDEX(Data!D:D,MATCH(Cover!$C$9,Data!$C:$C,0))

Data!D is the column of data i want, and in column C in "cover" i have a list of IDs, and it looks up that ID in column C of Data, and then gives me column D. between the two end )s, i have added a +1 to give me this...

=INDEX(Data!D:D,MATCH(Cover!$C$9,Data!$C:$C,0)+1)

I put this in the cell below the original formula and it gives me multiple data, which is great if that "ID" has 2 jobs i need. Is there some kind of sum product or match formula I can do against the original formula to see if the ID still matches, as with my version, it is only matching the first instance then going down one row, i would prefer it to go down one row and then see if the IDs still match, if they do great pull the data, but if they dont leave it blank.

then i can pull this formula down +2, +3 etc and have it for 100 rows, and if an ID has 100 jobs it will work, and if it has 1, it will work.

As at the moment it is pulling rows of data for people listed after this ID im looking for.

any clues? thanks

 

[cyrilbrd]

do you want to return multiple match? example if ID YTY567 is repeated three times (3), you would like to see all three matches?
Note try avoid using whole column ranges, it will be faster to calculate if you use specific ranges...

Excel 2010
	A	B	C	D
1	value		match	index
2	4	they	1	this
3	3	those	2	that
4	4	them	3	those
5	1	this	3	these
6	1	themselves	4	they
7			4	them
8			4	their
9			2	theirs
10			1	themselves
boniouk

 

[Boniouk]

I put this on the end of my index match, which seemed to look for additional matches, not sure how it works, but it does. Thanks again

ROW(Data!$A$2:$A$1000)-ROW(Data!$A$2)+1),ROW(1:1))))

 

[cyrilbrd]

Based on data provided in post#2, formula in B2 is =IF(N(A2),INDEX(D$2:D$10,SMALL(IF(C$2:C$10=A2,ROW(C$2:C$10)-ROW(B$2)+1),COUNTIF(A$2:A2,A2))),"") Ctrl + Shift + Enter not just enter on a PC or Command + Return on a MAC.