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 a couple tweaks needed:
1) Can you seperate each match with "; "?
2) Can a no match return: "$F5: N/A" ?
Thanks
Just a couple tweaks needed:
1) Can you seperate each match with "; "?
2) Can a no match return: "$F5: N/A" ?
Thanks
=F5&": "&TEXTJOIN(";",,IF(SUM(LEN(BYROW(INDIRECT("'"&$F5&"'!A:A"),
LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),"")))))=0,"NA",BYROW(INDIRECT("'"&$F5&"'!A:A"),
LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),"")))))&" - "&$G$5
You are welcome. This formula uses ; instead of , and returns NA if no exact match is found. JosWoolley has explained why calculation might take some time, thank you.
- 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!=$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?
