Forum Discussion

JimK2112's avatar
JimK2112
Copper Contributor
May 12, 2026

XLOOKUP using 3 unique criteria

I have almost completed my pet project, but I have one final piece that I cannot figure out.

 

First off, I work in insurance where I audit incoming (uploaded) spreadsheets from insurance reps.  The auditing I do mainly is to be sure that all the data in the uploaded spreadsheet imports into our db accurately and completely.  These uploaded spreadsheets list any new, cancelled or adjusted policy information.  The policy could be one carrier or it could be multiple carriers.  I built a dashboard (sorta) that sorts the rows of data in a way that makes it a TON easier to audit.  My main focus here is to list out all the carriers of a policy and to show their cost related columns for each carrier's part.

 

I am using a FILTER formula to list the carriers based off a policy # that is a Data Validation list in cell (Dashboard!A4).  The carriers are then listed in cells (Dashboard!AN4 - AN25).  Then, in columns (Dashboard!AO - AS) are the cost related columns.  The Data Validation list, Carriers and cost related columns are all pulled from the Transactions worksheet.  So far, this is working MOSTLY as intended, but because some policies use the same carriers, the cost related columns are not filtering by policy and will then therefor show the first available entry for each carrier (kinda like what VLOOKUP does).

 

I am using the following formulas so far.

 

List of carriers:

=FILTER(Transactions!H:H, Transactions!A:A=A4, "No matches found").  This works as intended.

 

Cost related columns:

=XLOOKUP(AN5,Transactions!$H$3:$H$1000,XLOOKUP(Transactions!$O$2,Transactions!$A$2:$BA$2,Transactions!$A$3:$BA$1000)).  This too works as (mostly) intended, but as you can see, I need an additional filter to sort by policy #.  This should be the last step to finishing this pet project.

 

I would appreciate it if anyone can help me add the additional formula criteria that would now filter out by specific policy.  I tried adding another XLOOKUP to the formula to add the additional criteria based on policy # with no luck.  For additional reference, the Transaction worksheet will list the same policy for each row that contains a unique carrier.  So if a policy has 5 unique carriers, there will be 5 rows to list each unique carrier and their cost related amounts associated with each carrier (row).  What I need is to add the additional filter based off the policy # to be sure that the cost related columns are specific to the policy # and individual carriers?

 

Any help would be GREATLY appreciated.  Thanks

4 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor

    For your cost-related columns, use this formula structure:

    =XLOOKUP(1,

        (Transactions!$A$3:$A$1000=$A$4) * (Transactions!$H$3:$H$1000=AN5),

        XLOOKUP(Transactions!$O$2, Transactions!$A$2:$BA$2, Transactions!$A$3:$BA$1000))

     

    If you prefer a FILTER-based approach:

    =FILTER(

        XLOOKUP(Transactions!$O$2, Transactions!$A$2:$BA$2, Transactions!$A$3:$BA$1000),

        (Transactions!$A$3:$A$1000=$A$4) * (Transactions!$H$3:$H$1000=AN5),

    "No match found")

     

    If your cost columns in Dashboard (AO:AS) have headers matching the Transaction sheet headers, you could use:

    =XLOOKUP(1,

        (Transactions!$A$3:$A$1000=$A$4) * (Transactions!$H$3:$H$1000=$AN5),

        XLOOKUP(AO$4, Transactions!$A$2:$BA$2, Transactions!$A$3:$BA$1000))

    Where AO$4 contains the header name for that cost column (then drag across).

    • JimK2112's avatar
      JimK2112
      Copper Contributor

      Thank you for your suggestions.  I also posted this on another Excel forum and got the following suggestions:

      Listing the carriers: (This still works as intended) - Note: No slowdown noticed when using this formula alone.

      =FILTER(Transactions!H3:H1000, Transactions!A3:A1000=A4, "No carriers found")

       

      Cost related columns: (This too works as needed, but now the workbook has really slowed down)

      =XLOOKUP(AN4&Policy,Transactions!H:H&Transactions!A:A,Transactions!O:O,"")

       

      Because of the slowdown, I will try your suggestions tonight to see if it brings the speed back up.  In the meantime, I would appreciate any insight you may have as to why this xlookup has bogged my workbook down.  I do realize that there is a good amount of additional data gathering going with this new section though.  The slowdown is when I use the dropdown (Dashboard!A4) to pick a policy.  It takes a good 3 - 5 seconds for the data to adjust throughout the entire Dashboard (not just this new section).  Even a copy/paste on a different sheet takes a few seconds and always brings up the hourglass for that few seconds too.  I will also ask the person that suggested this formula to see if he has any ideas on this too.

       

  • Harun24HR's avatar
    Harun24HR
    Silver Contributor

    You can use FILTER() function easily to meet 3 criteria. Please share a sample file and show your input and desired output. You may attach the file in post or via GoogleDrive, OneDrive, Dropbox etc.

    • JimK2112's avatar
      JimK2112
      Copper Contributor

      Thank you for your response.  I will share a sample file when I get home tonight.  This will be the same sample file I used when I posted this issue in a different Excel forum and it produced a few weird errors (because I rebuilt it rather quickly and didn't worry about perfection) that do not affect anything important and I will declare those issues when I post the sample file.