Forum Discussion

JimK2112's avatar
JimK2112
Occasional Reader
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

1 Reply

  • 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.