Forum Discussion

dshawSLDC's avatar
dshawSLDC
Brass Contributor
Jun 10, 2021

Xlookup Return second match.

I have a table like this District         Student ABC                John JKL                 Cindy ABC                Greg Artesia           Denice  ABC               Aaron   Im using this X...
excel
Formulas and Functions
Hogstad_Raadgivning's avatar
Hogstad_Raadgivning
Iron Contributor
Jun 11, 2021

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

  • dshawSLDC's avatar
    dshawSLDC
    Brass Contributor
    Jun 11, 2021

    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's avatar
    dshawSLDC
    Brass Contributor
    Jun 11, 2021

    Hogstad_Raadgivning 

    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.

Resources