# Issue: Only returning 1st match on Index/Match

1. ## Issue: Only returning 1st match on Index/Match

Hi Guys,

I have a bunch of unique identification numbers and the ID numbers cannot be used more than once.

ID number eligibility is determined by the index/match function (4 criteria). (formula works up to this point)
{=INDEX(Table!\$A\$4:\$AT\$80,MATCH(1,(Table!\$K\$4:\$K\$80=K11)*(Table!\$AT\$4:\$AT\$80=M11)*(Table!\$N\$4:\$N\$80=O11)*(Table!\$AQ\$4:\$AQ\$80='1st result fail'!B11),0),1)}

Where the formula falls apart is that it will only return the first ID number that meets the criteria. I need it to return a unique ID number that meets the index/match function criteria each time.

Any ideas?

Sincerely,
Taylor  Register To Reply

2. ## Re: Issue: Only returning 1st match on Index/Match

So on your formula sheet, you have this criteria
ABC KS 08 Yellow multiple times. Do you want it to bring back a different ID each time?
What if the # of times it's on formula sheet is greater than the # of times it's in Table?
For example, you have those criteria three times in 1st result fail and only 2 times in Table. What does it return the third time?
What if the number of times it's on formula sheet is less than the # of times its in Table?  Register To Reply

3. ## Re: Issue: Only returning 1st match on Index/Match

Maybe

Put in A6 and press CTRL-SHIFT-ENTER button together and copied down and cross :

=INDEX(Table!\$A\$4:\$A\$80,SMALL(IF((Table!\$K\$4:\$K\$80=\$K6)*(Table!\$N\$4:\$N\$80=\$O6)*(Table!\$AQ\$4:\$AQ\$80=\$B6)*(Table!\$AT\$4:\$AT\$80=\$M6),ROW(Table!\$A\$4:\$A\$80)-ROW(Table!\$A\$4)+1),COUNTIFS(\$B\$6:B6,B6,\$K\$6:K6,K6,\$M\$6:M6,M6,\$O\$6:O6,O6)))

Cheers  Register To Reply