Xlookup Return second match.

%3CLINGO-SUB%20id%3D%22lingo-sub-2437349%22%20slang%3D%22en-US%22%3EXlookup%20Return%20second%20match.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2437349%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20like%20this%3C%2FP%3E%3CP%3E%3CSTRONG%3EDistrict%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BStudent%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EABC%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20John%3C%2FP%3E%3CP%3EJKL%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BCindy%3C%2FP%3E%3CP%3EABC%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Greg%3C%2FP%3E%3CP%3EArtesia%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BDenice%26nbsp%3B%3C%2FP%3E%3CP%3EABC%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BAaron%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIm%20using%20this%20Xlookup%20Formula%20--%26nbsp%3B%3DXLOOKUP(ABC%2CTable1%5BDistrict%5D%2CTable1%5BStudent%5D%2C%22District%20not%20found%22%2C0%2C1)%20--%3C%2FP%3E%3CP%3EIt%20will%20only%20show%20me%20the%20first%20match%20of%20%22John%22%20How%20would%20i%20get%20it%20to%20show%20the%20second%26nbsp%3B%20or%26nbsp%3B%20third%20match%3F%3C%2FP%3E%3CP%3EThanks%20for%20any%20assitance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2437349%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2437648%22%20slang%3D%22en-US%22%3ERe%3A%20Xlookup%20Return%20second%20match.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2437648%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1076230%22%20target%3D%22_blank%22%3E%40dshawSLDC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20dont%20think%20you%20can%20do%20that%20with%20Xlookup.%20But%20if%20you%20have%20Xlookup%2C%20you%20also%20might%20have%20Filter%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFilter%20rows%20with%20your%20conditition.%20Select%20wanted%20row%20using%20Index%3A%3C%2FP%3E%3CP%3E%3DINDEX(FILTER(Tabell1%3BTabell1%5BDistrict%5D%3D%22Abc%22)%3BG2)%26nbsp%3B%20Where%20G2%20is%20the%20row%20number%20you%20want.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2441232%22%20slang%3D%22en-US%22%3ERe%3A%20Xlookup%20Return%20second%20match.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2441232%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20partially%20works%20if%20i%20switch%20it%20to%20column%20instead%20of%20row.%20Do%20you%20know%20if%20there%20is%20a%20way%20to%20only%20show%20the%202nd%20match%20or%20third%2C%20fourth%2C%20nth....%20%3F%20I%20have%20attached%20an%20example%20I%20would%20like%20the%20ability%20to%20add%20spaces%20between%20the%20retuned%20matches%20depending%20on%20certain%20criteria.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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.