Xlookup Return second match.

Question

I have a table like thisDistrict&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;StudentABC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; JohnJKL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CindyABC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GregArtesia&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Denice&nbsp;ABC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Aaron&nbsp;Im using this Xlookup Formula --&nbsp;=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&nbsp; or&nbsp; third match?Thanks for any assitance.

hogstad_raadgivning · Answer

Hi,&nbsp;dshawSLDC&nbsp;&nbsp;I dont think you can do that with Xlookup. But if you have Xlookup, you also might have Filter?&nbsp;&nbsp;Filter rows with your conditition. Select wanted row using Index:=INDEX(FILTER(Tabell1;Tabell1[District]="Abc");G2)&nbsp; Where G2 is the row number you want.&nbsp;&nbsp;Best Regards- Geir

dshawsldc · Answer

Hogstad_Raadgivning&nbsp;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.&nbsp;Thanks again for your help.

dshawsldc · Answer

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.

detlef_lewin · Answer

dshawSLDC&nbsp;Your descriptions don't match what you are trying to achieve.&nbsp;

Forum Discussion

Xlookup Return second match.

4 Replies

Resources