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
excelmee's avatar
excelmee
Copper Contributor
Jun 24, 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
    Aug 31, 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