Forum Discussion

Brass Contributor

Oct 07, 2023

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 ...

excel

Excel on mobile

Formulas and Functions

office 365

Cmoreno89

Brass Contributor

Oct 07, 2023

Thank you,

Just a couple tweaks needed:
1) Can you seperate each match with "; "?
2) Can a no match return: "$F5: N/A" ?

Thanks

OliverScheurich

Gold Contributor

Oct 08, 2023

Cmoreno89

=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.

Cmoreno89
Brass Contributor
Oct 08, 2023
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!
- OliverScheurich
  Gold Contributor
  Oct 08, 2023
  Cmoreno89
  =$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.
  - Cmoreno89
    Brass Contributor
    Oct 08, 2023
    Thank you, but I'm getting "#NUM!" and "#N/A" errors when I copy and paste the formula down the column 😞
    
    Any suggestions?