I need help creating a formula that will show data from columns D through G when both conditions are met at the same time.
Here’s what I’m trying to do:
Check if values in column J match values in column B
Also check if values in column K match values in column C
When both conditions are true, display the corresponding row data from columns D to G
If there are no matches found, just leave those rows blank
I’ve been trying different approaches but can’t seem to get the formula right. The data needs to be filtered based on these two matching conditions working together, not separately. Has anyone dealt with this type of multi-condition filtering before? Any suggestions would be really helpful!
I encountered a similar challenge when building reports that needed cross-referencing between multiple columns. While FILTER works well, I found using QUERY can be more flexible for complex matching scenarios. Try something like =QUERY(D:K,"SELECT D,E,F,G WHERE J=B AND K=C") but you’ll need to adjust the syntax since QUERY doesn’t directly compare columns like that. What actually worked better for me was using ARRAYFORMULA with IF statements: =ARRAYFORMULA(IF((J:J=B:B)*(K:K=C:C),D:G&E:G&F:G&G:G,"")) though you’d need to separate the columns properly. Another approach is using INDEX and MATCH functions nested together, which gives you more control over error handling. The main thing I learned is that when dealing with multiple criteria, it’s crucial to ensure your data ranges are exactly the same size, otherwise you’ll get dimension errors that are frustrating to debug.
try using xlookup if you have it available - its way more intuitive than filter for this kinda thing. something like =IF(AND(XLOOKUP(J1,B:B,B:B,0)=J1,XLOOKUP(K1,C:C,C:C,0)=K1),D1:G1,"") might work better. i’ve found xlookup handles multiple criteria more reliably than the old functions tbh
This sounds like a perfect case for the FILTER function combined with multiple conditions. You can achieve this with a formula like =FILTER(D:G,(J:J=B:B)*(K:K=C:C)). The asterisk acts as an AND operator, ensuring both conditions must be satisfied simultaneously. I ran into similar issues when working with customer data matching across different sheets. The key insight is that FILTER naturally handles empty results by showing nothing for non-matching rows, which aligns with your requirement to leave blank rows when no matches exist. Make sure your data ranges are consistent and don’t include headers in the comparison ranges if they contain text. If you’re getting errors, try wrapping it with IFERROR to handle edge cases: =IFERROR(FILTER(D:G,(J:J=B:B)*(K:K=C:C)),""). This approach has been reliable in my experience with multi-criteria filtering tasks.