Forum Discussion

JMunoz4's avatar
JMunoz4
Copper Contributor
Feb 12, 2021

Help - Find the 2nd match using Xlookup

Is there a way using XLookup to return the 2nd (or 3rd, or 4th, or whatever) match in the lookup value? I'm also trying to use as few helper columns as I can. Otherwise I'd just use the VLookup funct...
excel
JMunoz4's avatar
JMunoz4
Copper Contributor
Feb 12, 2021

Riny_van_Eekelen 

Thank you. I am still curious if XLOOKUP can return the nth match in an array. But now that you mention it, redoing the pivot table would be the best way to display what I'm looking for. I don't have much experience with pivot tables so I sorta reverse engineered one from a similar worksheet.

excelmee's avatar
excelmee
Copper Contributor
Jun 25, 2021

JMunoz4 

 

It seems possible. Here is an example.

 

Formula:

 

=XLOOKUP(
   F2:F5&H1,
   B1:B16&
      SORTBY(
        SEQUENCE(ROWS(B1:B16),1,2)-MATCH(SORT(B1:B16),SORT(B1:B16),0),
        SORTBY(SEQUENCE(ROWS(B1:B16),1,2),B1:B16,1),1
      ),
   C1:C16
)

 

https://excelmee.com/excel-formulas/lookup/xlookup-nth-occurrence-in-excel-first-last-and-nth/

  • LSpurchasing's avatar
    LSpurchasing
    Copper Contributor
    Aug 31, 2021
    excelmee
    Would this formula need to be revised to find the nth result when using only one lookup value (F2)? I tried the formula you posted and it didn't work in such a case.
    • excelmee's avatar
      excelmee
      Copper Contributor
      Sep 01, 2021

      LSpurchasing You just need to change F2:F5&H1 to F2&H1.

      If you have a single search key better follow the Index + Filter combo.

Resources