Forum Discussion
Formula for returning multiple exact matches
Hi all, I've been working on a formula for several days now but haven't been able to fully figure it out. ChatGPT helped me get to this point but it can't figure out how to completely solve ...
Thank you!
Just need 1 more tweak! Can you please make it so that when there is no match it returns: "$F5: N/A" instead of "$F5: N/A - $G$5". ? Thanks!
Just need 1 more tweak! Can you please make it so that when there is no match it returns: "$F5: N/A" instead of "$F5: N/A - $G$5". ? Thanks!
=$F5&": "&TEXTJOIN(";",,IF(SUM(LEN(BYROW(
INDIRECT("'"&$F5&"'!A1:A10000"),
LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),"")))))=0,"NA",
BYROW(INDIRECT("'"&$F5&"'!A1:A10000"),
LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),"")))))
&IF(SUM(LEN(BYROW(INDIRECT("'"&$F5&"'!A1:A10000"),
LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),"")))))=0,""," - "&$G$5)
You are welcome. This formula returns the intended output in my sheet.
- Thank you, but I'm getting "#NUM!" and "#N/A" errors when I copy and paste the formula down the column 😞
Any suggestions?Does it work if you drag the formula down? Otherwise can you attach a file without sensitive data which shows the error messages?
- The errors occured after I copy/pasted the initial formula down the same column. Does this formula search for EXACT matches? Seems like excel is confused from the formula commands? Perhaps a partial match messed up the commands?