Jun 10 2021 03:44 PM - edited Jun 10 2021 05:06 PM
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.
Jun 10 2021 06:05 PM
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
Jun 11 2021 03:07 PM
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.
Jun 11 2021 04:09 PM - edited Jun 11 2021 04:10 PM
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.
Jun 11 2021 04:46 PM