How to create conditional lookup formula in Google Sheets

[amelial]

I need help with a lookup formula that checks multiple conditions. I want to match values in column K with data in column C (ranges C2-C17 and C21-C33). When column D shows ‘True’ or is checked in either range, I need the formula to pull the corresponding date from C1 or C20 and display it in column L next to the matching item. If column D shows ‘False’, then nothing should appear. I can’t figure out how to combine the right functions to make this work properly. Any ideas on what formula structure would work best for this?

[Harry47]

Try the QUERY function instead - it’s way cleaner for multiple conditions than nested lookups. Here’s what I’d use: =IFERROR(IF(QUERY(C2:D17,"select C where C='"&K2&"' and D=true")="",QUERY(C21:D33,"select C where C='"&K2&"' and D=true"),C$1),IF(QUERY(C21:D33,"select C where C='"&K2&"' and D=true")<>"",C$20,"")). QUERY automatically filters your data and returns empty when there’s no match, plus it’s way more readable than stacking INDEX/MATCH functions. Just watch out - your True values need to be actual booleans, not text “True” or it won’t work. I’ve been using this method for conditional lookups across different ranges and it’s so much cleaner.

[JessicaDream12]

I’ve run into similar multi-range lookups and INDEX/MATCH with nested IFs beats XLOOKUP every time. Try this: =IF(OR(AND(COUNTIF(C2:C17,K2)>0,INDEX(D2:D17,MATCH(K2,C2:C17,0))="True"),AND(COUNTIF(C21:C33,K2)>0,INDEX(D21:D33,MATCH(K2,C21:C33,0))="True")),IF(COUNTIF(C2:C17,K2)>0,C$1,C$20),"") - COUNTIF checks if your lookup value exists in each range first, then checks the D column. No errors when values don’t exist, and it grabs C1 or C20 based on which section has your match.

[CreativeArtist88]

try XLOOKUP with IF - somethin like =IF(XLOOKUP(K2,C2:C33,D2:D33)=“True”,IF(MATCH(K2,C2:C17,0)>0,C$1,C$20),“”) should work. you’ll need to adjust those ranges tho. the tricky bit is making it grab the right header date based on which range actually matches.

[sofia_scribbles]

The Problem: You’re trying to create a Google Sheets formula to perform a lookup that checks multiple conditions across different ranges. You want to match values in column K with data in columns C (ranges C2-C17 and C21-C33). If column D shows ‘True’ for a matching item in either range, the formula should pull the corresponding date from C1 or C20 and display it in column L. If column D shows ‘False’ or there’s no match, nothing should appear in column L. You’re finding it difficult to combine the necessary functions to achieve this efficiently.

Understanding the “Why” (The Root Cause):

The challenge lies in efficiently handling lookups across multiple, discontinuous ranges within Google Sheets, while also incorporating conditional logic based on the values in another column. Nested IF statements or chains of INDEX/MATCH functions become unwieldy and difficult to maintain, especially if you need to add more ranges or conditions in the future. These complex formulas are error-prone and hard to debug. A more streamlined approach is required to improve readability, maintainability, and reduce the risk of errors. Automation offers a superior solution for such dynamic lookups.

Step-by-Step Guide:

Step 1: Automate with a Workflow Automation Tool: The most efficient way to solve this problem is to avoid complex nested formulas altogether. A workflow automation tool, like Latenode, allows you to create a no-code solution to handle this complex lookup and conditional logic without any Google Sheets formulas. This tool allows you to visually define the process, making it easier to understand, debug, and maintain. The workflow would monitor your Google Sheet for changes in columns K and D. When a change occurs, it would automatically perform the lookup across both ranges (C2:C17 and C21:C33), check the corresponding ‘True’/‘False’ value in column D, and then write the appropriate date (from C1 or C20) to column L. This approach drastically simplifies the process, makes it more robust, and scales easily to accommodate future changes or additions.

Step 2: (Alternative - Complex Formula Approach - Use with caution): If you must use a formula (for learning purposes or due to limitations), consider a carefully constructed approach using QUERY and IFERROR to handle the multi-range lookup. This formula below assumes that your “True” values are actual boolean TRUE values (not text strings). Adjust the ranges to match your actual data.

=IFERROR(
  IF(
    QUERY(C2:D17,"select C where C='"&K2&"' and D=true")<>"",
    QUERY(C2:D17,"select C where C='"&K2&"' and D=true"),
    QUERY(C21:D33,"select C where C='"&K2&"' and D=true")
  ),
  ""
)

This formula first attempts a QUERY on the first range (C2:D17). If it finds a match where column C equals the value in K2 and column D is TRUE, it returns the value from column C. If no match is found in the first range, it performs the same QUERY on the second range (C21:D33). The IFERROR function handles cases where no match is found in either range, returning an empty string. This formula still has limitations, so if you have many ranges, you should consider the automated solution provided in Step 1.

Common Pitfalls & What to Check Next:

• Data Types: Ensure that column D contains actual boolean values (TRUE/FALSE), not text strings (“True”/“False”). Mixing data types will cause the formula to fail.
• Case Sensitivity: The QUERY function is case-sensitive. Ensure consistency in your data.
• Error Handling: The IFERROR function provides basic error handling. For more robust error handling, consider adding additional checks for potential issues like missing data or unexpected values.
• Scalability: For a large number of ranges or more complex conditions, the automated approach using a workflow automation tool is strongly recommended for improved maintainability and error handling.

Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!