Complex Formula Help

[Kkathol]

@shamimarita I have a formula that doesn't seem to be doing what I need it to do. It is pasted below.

The purpose is to find viable titles and rates for employees based on their credentials at their firm and searching the client's title requirements to map which client titles could be used for the employees at the firm. The first sheet/tab is the "calculator" where employee criteria is entered at the top and the results from the search spill into the Client Title Options column. The second sheet/tab is where all the Rate & Title data lives that we are searching through. We want the formula to follow these rules:

Stipulation A - Search column M (Firm Titles) of the Client Approved Rates Titles sheet for the title matching B5 of the Rate & Title Calculator to finds all titles in column C (Client Titles) of the Client Approved Rates Titles sheet where the following is true:
1. If column I (Client - License Required) of the Client Approved Rates Titles sheet is “No” and column J (Client - Degree Required) is “No”, column N (Firm - License Required) is “No” or “Yes” and column O (Firm - Degree Required) is “No” or “Yes” or
2. If column I of the Client Approve Rates Titles sheet is “Yes” and column J is “No”, column N is “Yes” and column O is “No” or “Yes” or
3. If column I of the Client Approved Rates Titles sheet is “No” and column J is “Yes”, column N is “No” or “Yes” and column O is “Yes” or
4. If column I of the Client Approved Rates Titles sheet is “Yes” and column J is “Yes”, column N is “Yes” and column O is “Yes”


Stipulation B – Filter through the Stipulation A results to find which of them from the Client Approved Rates Titles sheet follow these rules:
1. The value in column B (Year) is an exact match to the value in cell H5 of the Rate & Title Calculator sheet
2. The values in column F (Location) must be an exact match to the value in cell F5 of the Rate & Title Calculator sheet
3. The value in column E (Rate) is greater than or equal to the value in cell E5 of the Rate & Title Calculator sheet

Stipulation C – After determining if a title follows both stipulation A and stipulation B:
1. Check column D (Discipline) in Client Approved Rates Titles to make sure it is an exact match to the value in cell C5 of the Rate & Title Calculator sheet IF a value is entered in C5 otherwise ignore C5 if it is blank
2. If column I (License Required) for the resulting title rows from the Client Approved Rates Titles sheet equals “Yes” check to make sure G5 of the Rate & Title Calculator sheet equals “Yes”, otherwise ignore the value in G5 if other criteria does not match for that title or if column I is "No"

Finally, return the final, resulting Client Titles from column C of the Client Approved Rates Titles sheet that at least meet the A & B stipulations into column B (Client Title Options) of the Rate & Title Calculator sheet starting at B11 and spill results down column B if there are multiple results.

The formula is currently returning "No matching titles" for everything even when there should be results. For example:

Firm Title = Senior Coordinator
Employee Discipline = Project Support
Minimum Bill Rate = $115.00 (after calculation in the column from Cost Rate column)
Employee Location = Kansas
License = No
Year = 2026

An "Assistant Project Manager I" from the Client Approved Rates Titles matches the first criteria that we're comparing to the Firm Titles criteria (Columns I, J, N, O) and even when the second set of criteria matches (Year, Location, Minimum Rate), the results do not return "Assistant Project Manager I" or any titles into column B for Client Title Options.

=IFERROR(INDEX(FILTER(FILTER(FILTER('Client Approved Rates Titles'!A1434:O2229,('Client Approved Rates Titles'!M1434:M2229='Rate & Title Calculator'!B5)*((('Client Approved Rates Titles'!I1434:I2229="No")*('Client Approved Rates Titles'!J1434:J2229="No"))+(('Client Approved Rates Titles'!I1434:I2229="Yes")*('Client Approved Rates Titles'!J1434:J2229="No")*('Client Approved Rates Titles'!N1434:N2229="Yes"))+(('Client Approved Rates Titles'!I1434:I2229="No")*('Client Approved Rates Titles'!J1434:J2229="Yes")*('Client Approved Rates Titles'!O1434:O2229="Yes"))+(('Client Approved Rates Titles'!I1434:I2229="Yes")*('Client Approved Rates Titles'!J1434:J2229="Yes")*('Client Approved Rates Titles'!N1434:N2229="Yes")*('Client Approved Rates Titles'!O1434:O2229="Yes")))),(INDEX(FILTER('Client Approved Rates Titles'!A1434:O2229,('Client Approved Rates Titles'!M1434:M2229='Rate & Title Calculator'!B5)*((('Client Approved Rates Titles'!I1434:I2229="No")*('Client Approved Rates Titles'!J1434:J2229="No"))+(('Client Approved Rates Titles'!I1434:I2229="Yes")*('Client Approved Rates Titles'!J1434:J2229="No")*('Client Approved Rates Titles'!N1434:N2229="Yes"))+(('Client Approved Rates Titles'!I1434:I2229="No")*('Client Approved Rates Titles'!J1434:J2229="Yes")*('Client Approved Rates Titles'!O1434:O2229="Yes"))+(('Client Approved Rates Titles'!I1434:I2229="Yes")*('Client Approved Rates Titles'!J1434:J2229="Yes")*('Client Approved Rates Titles'!N1434:N2229="Yes")*('Client Approved Rates Titles'!O1434:O2229="Yes")))),,2)='Rate & Title Calculator'!H5)*(INDEX(FILTER('Client Approved Rates Titles'!A1434:O2229,('Client Approved Rates Titles'!M1434:M2229='Rate & Title Calculator'!B5)*((('Client Approved Rates Titles'!I1434:I2229="No")*('Client Approved Rates Titles'!J1434:J2229="No"))+(('Client Approved Rates Titles'!I1434:I2229="Yes")*('Client Approved Rates Titles'!J1434:J2229="No")*('Client Approved Rates Titles'!N1434:N2229="Yes"))+(('Client Approved Rates Titles'!I1434:I2229="No")*('Client Approved Rates Titles'!J1434:J2229="Yes")*('Client Approved Rates Titles'!O1434:O2229="Yes"))+(('Client Approved Rates Titles'!I1434:I2229="Yes")*('Client Approved Rates Titles'!J1434:J2229="Yes")*('Client Approved Rates Titles'!N1434:N2229="Yes")*('Client Approved Rates Titles'!O1434:O2229="Yes")))),,6)='Rate & Title Calculator'!I5)*(INDEX(FILTER('Client Approved Rates Titles'!A1434:O2229,('Client Approved Rates Titles'!M1434:M2229='Rate & Title Calculator'!B5)*((('Client Approved Rates Titles'!I1434:I2229="No")*('Client Approved Rates Titles'!J1434:J2229="No"))+(('Client Approved Rates Titles'!I1434:I2229="Yes")*('Client Approved Rates Titles'!J1434:J2229="No")*('Client Approved Rates Titles'!N1434:N2229="Yes"))+(('Client Approved Rates Titles'!I1434:I2229="No")*('Client Approved Rates Titles'!J1434:J2229="Yes")*('Client Approved Rates Titles'!O1434:O2229="Yes"))+(('Client Approved Rates Titles'!I1434:I2229="Yes")*('Client Approved Rates Titles'!J1434:J2229="Yes")*('Client Approved Rates Titles'!N1434:N2229="Yes")*('Client Approved Rates Titles'!O1434:O2229="Yes")))),,5)>='Rate & Title Calculator'!E5)),IF('Rate & Title Calculator'!C5="",TRUE,INDEX(FILTER('Client Approved Rates Titles'!A1434:O2229,('Client Approved Rates Titles'!M1434:M2229='Rate & Title Calculator'!B5)*((('Client Approved Rates Titles'!I1434:I2229="No")*('Client Approved Rates Titles'!J1434:J2229="No"))+(('Client Approved Rates Titles'!I1434:I2229="Yes")*('Client Approved Rates Titles'!J1434:J2229="No")*('Client Approved Rates Titles'!N1434:N2229="Yes"))+(('Client Approved Rates Titles'!I1434:I2229="No")*('Client Approved Rates Titles'!J1434:J2229="Yes")*('Client Approved Rates Titles'!O1434:O2229="Yes"))+(('Client Approved Rates Titles'!I1434:I2229="Yes")*('Client Approved Rates Titles'!J1434:J2229="Yes")*('Client Approved Rates Titles'!N1434:N2229="Yes")*('Client Approved Rates Titles'!O1434:O2229="Yes")))),,4)='Rate & Title Calculator'!C5)*IF('Rate & Title Calculator'!G5="Yes",INDEX(FILTER('Client Approved Rates Titles'!A1434:O2229,('Client Approved Rates Titles'!M1434:M2229='Rate & Title Calculator'!B5)*((('Client Approved Rates Titles'!I1434:I2229="No")*('Client Approved Rates Titles'!J1434:J2229="No"))+(('Client Approved Rates Titles'!I1434:I2229="Yes")*('Client Approved Rates Titles'!J1434:J2229="No")*('Client Approved Rates Titles'!N1434:N2229="Yes"))+(('Client Approved Rates Titles'!I1434:I2229="No")*('Client Approved Rates Titles'!J1434:J2229="Yes")*('Client Approved Rates Titles'!O1434:O2229="Yes"))+(('Client Approved Rates Titles'!I1434:I2229="Yes")*('Client Approved Rates Titles'!J1434:J2229="Yes")*('Client Approved Rates Titles'!N1434:N2229="Yes")*('Client Approved Rates Titles'!O1434:O2229="Yes")))),,9)="Yes",TRUE)),,3),"No matching titles")

 

[shamimarita]

Hello Kkathol,

It’s returning “No matching titles” mainly because the criteria are being applied in a very fragile way (repeating FILTER + INDEX many times), and there’s at least one cell mismatch in your Stipulation B:

• In your description, Location = F5, but your formula checks location against 'Rate & Title Calculator'!I5 (see the ...,6)='Rate & Title Calculator'!I5 part). If your employee location is in F5, that alone will zero out every match.

Also watch for hidden spaces / inconsistent text in titles and locations (very common): “Senior Coordinator” vs “Senior Coordinator ” will fail an exact match.

A cleaner, debuggable solution (single FILTER with LET)

Put this in B11 (Client Title Options spill):

=LET(
d,'Client Approved Rates Titles'!A1434:O2229,
firmTitle,TRIM('Rate & Title Calculator'!B5),
disc,TRIM('Rate & Title Calculator'!C5),
minRate,'Rate & Title Calculator'!E5,
empLoc,TRIM('Rate & Title Calculator'!F5),
yr,'Rate & Title Calculator'!H5,
empLic,'Rate & Title Calculator'!G5,

cTitle,CHOOSECOLS(d,3),
yearCol,CHOOSECOLS(d,2),
rateCol,CHOOSECOLS(d,5),
locCol,TRIM(CHOOSECOLS(d,6)),
discCol,TRIM(CHOOSECOLS(d,4)),
firmTitleCol,TRIM(CHOOSECOLS(d,13)),
cLicReq,TRIM(CHOOSECOLS(d,9)),
cDegReq,TRIM(CHOOSECOLS(d,10)),
fLicReq,TRIM(CHOOSECOLS(d,14)),
fDegReq,TRIM(CHOOSECOLS(d,15)),

Aok,(firmTitleCol=firmTitle)*
( ((cLicReq="No")*(cDegReq="No"))
+((cLicReq="Yes")*(cDegReq="No")*(fLicReq="Yes"))
+((cLicReq="No")*(cDegReq="Yes")*(fDegReq="Yes"))
+((cLicReq="Yes")*(cDegReq="Yes")*(fLicReq="Yes")*(fDegReq="Yes")) ),

Bok,(yearCol=yr)*(locCol=empLoc)*(rateCol>=minRate),

Cok,IF(disc="",TRUE,discCol=disc)*
IF(cLicReq="Yes", empLic="Yes", TRUE),


FILTER(cTitle, Aok*Bok*Cok, "No matching titles")
)

Why this works better

• It applies A + B + C once (no repeated nested FILTER/INDEX calls).
• It uses TRIM() on text fields to prevent invisible-space mismatches.
• It uses your stated Location cell F5 (change empLoc if your location input is elsewhere).

Quick checks if it still fails

1. Confirm the firm title values truly match: try =TRIM(B5)=TRIM(M1434) on a known matching row.
2. Confirm Year types match (number vs text). If column B is text “2026”, use VALUE() or -- on one side.
3. Confirm the Location input cell (F5 vs I5) matches your sheet layout.