Hey everyone, I’m stuck with a spreadsheet problem and need some advice.
My current function pulls every match from my data instead of just the ones that match my search criteria in cell D8. Here’s what I mean:
My data looks like this:
#VENDORCODE#98765_DATE_SERIAL_Coffee#54321_DATE_SERIAL_Juice#98765_DATE_SERIAL_Milk
When I type VN/98765 in D8, I want to get back:
But what actually happens is I get everything:
Same thing when I put VN/54321 - should only show “Juice” but shows all three items instead.
Here’s my current formula:
=IF(ISBLANK(D8);"Enter search term";
LET(
search_pattern; "#" & REGEXEXTRACT(D8;"VN/(.*)") & "_";
sheet1_results; MAP(FILTER(DATA!A:A; FIND(search_pattern; DATA!A:A) > 0); LAMBDA(row;
LET(
values; MAP(SPLIT(row; "#"); LAMBDA(segment;
REGEXREPLACE(REGEXEXTRACT(TRIM("" & segment); "([\d\.,]+)$"); "\.";"") * 1
));
MAP(SEQUENCE(1; COLUMNS(values)); LAMBDA(col; IF(col = 1; ; INDEX(values;;col))))
)
));
sheet2_results; MAP(FILTER(BACKUP!A:A; FIND(search_pattern; BACKUP!A:A) > 0); LAMBDA(row;
LET(
values; MAP(SPLIT(row; "#"); LAMBDA(segment;
REGEXREPLACE(REGEXEXTRACT(TRIM("" & segment); "([\d\.,]+)$"); "\.";"") * 1
));
MAP(SEQUENCE(1; COLUMNS(values)); LAMBDA(col; IF(col = 1; ; INDEX(values;;col))))
)
));
flat1; FLATTEN(sheet1_results);
flat2; FLATTEN(sheet2_results);
final_output; TEXTJOIN("; "; TRUE;
IFERROR(FILTER(flat1; INDEX(flat1;;1) <> ""););
IFERROR(FILTER(flat2; INDEX(flat2;;1) <> "");)
);
IF(final_output = ""; "Nothing found"; TRANSPOSE(SPLIT(final_output; "; ")))
))
I need this to actually filter by the specific code instead of returning everything. What am I missing here?
Your regex pattern is too broad and catching everything. The issue is in your REGEXEXTRACT - it’s pulling the vendor code correctly but then your FIND function isn’t being strict enough about matching. Try changing your search_pattern line to include more context around the vendor code. Instead of just looking for the code followed by an underscore, make it search for the exact pattern including what comes after. Something like search_pattern; "#" & REGEXEXTRACT(D8;"VN/(.*)") & "_DATE_SERIAL_" should work better. This way it will only match entries that have your specific vendor code in the exact position rather than anywhere in the string. I had similar issues with partial matching in my inventory sheets and being more specific with the pattern solved it completely.
The core problem is with your FILTER logic - you’re filtering rows that contain the search pattern but then processing ALL segments within those rows regardless of whether they match your criteria. Your MAP function splits each row by “#” and processes every segment, not just the ones with your target vendor code. What you need is an additional filter step inside your MAP function to only extract segments that actually start with your search pattern. Try adding a condition like IF(LEFT(segment, LEN(search_pattern)) = search_pattern, your_processing_logic, "") within your LAMBDA function. This way you’ll only process and return the segments that truly match your vendor code rather than everything from matching rows. I ran into this exact issue when building similar lookup functions and adding that inner filter made all the difference.
looks like your FIND function is causing issues - it’s finding partial matches instead of exact ones. try using SEARCH with wildcards or modify your search_pattern to be more specific like “#98765_” instead of just “#98765_”. the problem is its matching any occurrence of those digits anywhere in the string