Hello everyone! I’m facing a challenge with my Google Sheets and could really use your advice. I have two columns, A and B, where I keep track of orders and their statuses, some of which are cancelled. In columns C and D, I have details about the products and their materials. What I need is to create a formula that checks if an order is cancelled and, if it is, pulls that cancelled order’s data along with the corresponding product material to show in my final worksheet. I’ve tried a few methods but I’m not having much luck. Has anyone tackled this issue before? What strategy would you recommend for matching and displaying only the cancelled orders and their associated materials?
Had a similar issue with returns data at my last job. Best solution I found was using FILTER if you’ve got the newer Google Sheets: =FILTER({A:A,D:D}, B:B=“cancelled”). This pulls both cancelled orders from column A and their materials from column D at once. Don’t have FILTER? Try =IF(B2=“cancelled”, A2&" - "&D2, “”) and drag down, then copy/paste special values to ditch the blanks. Double-check your data ranges match up and watch for extra spaces in the status column - they’ll break your matches. I usually trim the data first to avoid headaches.
VLOOKUP works great for this. Had the same issue tracking inventory returns - used VLOOKUP with an IF wrapper and it’s been solid: =IF(B2=“cancelled”, VLOOKUP(A2, A:D, 4, FALSE), “”). Just make sure your order ID in column A matches exactly and it’ll pull from column D. Easy to modify which column you want by changing the index number. Pro tip: keep your “cancelled” text consistent - even trailing spaces will break your matches (learned that one the hard way). Use absolute references if you’re copying the formula across multiple sheets.
try the QUERY function - it’s perfect for this. use =QUERY(A:D,“select A,D where B=‘cancelled’”) and it’ll pull all canceled orders with materials automatically. watch out for typos in your status column tho - “Cancelled” vs “cancelled” matters since QUERY is case-sensitive. way better than IF formulas when you’re dealing with tons of data.