Xlookup Return second match.

Contributor

I have a table like this

District         Student

ABC                John

JKL                 Cindy

ABC                Greg

Artesia           Denice 

ABC               Aaron

 

Im using this Xlookup Formula -- =XLOOKUP(ABC,Table1[District],Table1[Student],"District not found",0,1) --

It will only show me the first match of "John" How would i get it to show the second  or  third match?

Thanks for any assitance.

4 Replies

Hi, @dshawSLDC 

 

I dont think you can do that with Xlookup. But if you have Xlookup, you also might have Filter? 

 

Filter rows with your conditition. Select wanted row using Index:

=INDEX(FILTER(Tabell1;Tabell1[District]="Abc");G2)  Where G2 is the row number you want. 

 

Best Regards

- Geir

@Geir Hogstad 

This partially works if i switch it to column instead of row. Do you know if there is a way to only show the 2nd match or third, fourth, nth.... ? I have attached an example I would like the ability to add spaces between the retuned matches depending on certain criteria. 

Thanks again for your help.

This Did the Trick
{=INDEX(Data[Student],SMALL(IF(Data[District]=Invoice!A7,ROW(Data[District])-ROW(INDEX(Data[District],1,1))+1),2))}

Now I just Need to find away to stop when it reaches the last row.

@dshawSLDC 

Your descriptions don't match what you are trying to achieve.