Forum Discussion
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
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
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")
appreciate the effort. that resulted in #SPILLL!
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)
