Forum Discussion

ShaneatWork's avatar
ShaneatWork
Copper Contributor
Apr 25, 2025
Solved

Need help with Excel formula

=IFERROR(INDEX(TBL_REG[Appointment Effective Date],MATCH(TBL_SF[@[License '#]],TBL_REG[License Number],0)),"!-No Match")

 

Need some help with this formula.

It works fine however, in the TBL_REG table there are multiple rows that have the same [License Number] so it will return the first [Appointment Effective Date] it finds when matching [License '#] from the TBL_SF to the [License Number] in the TBL_REG table.. I would also like to match on [State] from the TBL_SF table to the [Appointment State] from the TBL_REG table. 

 

Thanks,
Shane

  • m_tarler's avatar
    m_tarler
    Apr 28, 2025

    As I noted, FILTER will return multiple values/lines if multiple match.  If you have that case then add either an INDEX or a TAKE to only output the 1st match:

    =TAKE(FILTER(TBL_REG[Appointment Effective Date], (TBL_SF[@[License '#]] = TBL_REG[License Number])*(TBL_SF[@[State]] = TBL_REG[State]), "!-No Match"),1)

     

4 Replies

  • m_tarler's avatar
    m_tarler
    Bronze Contributor

    You might consider using FILTER.  NOTE that FILTER can and will return multiple values/lines if multiple match:

    =FILTER(TBL_REG[Appointment Effective Date], (TBL_SF[@[License '#]] = TBL_REG[License Number])*(TBL_SF[@[State]] = TBL_REG[State]), "!-No Match")

      • m_tarler's avatar
        m_tarler
        Bronze Contributor

        As I noted, FILTER will return multiple values/lines if multiple match.  If you have that case then add either an INDEX or a TAKE to only output the 1st match:

        =TAKE(FILTER(TBL_REG[Appointment Effective Date], (TBL_SF[@[License '#]] = TBL_REG[License Number])*(TBL_SF[@[State]] = TBL_REG[State]), "!-No Match"),1)

         

Resources