For example:<\/p>\n
![\"\"](\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2024\/09\/A0004-1.png?resize=1024%2C414&ssl=1\")
<\/p>\n
=TEXTJOIN(“\\”, TRUE, A1:A5) <\/em><\/p>\n<\/div>\n<\/div>\n<\/div>\n This will combine the values in cells A1 to A5, ignoring empty cells, and separate them with a backslash. If we use FALSE as the second parameter:<\/p>\n =TEXTJOIN(“\\”, FALSE, A1:A5) <\/em><\/p>\n<\/div>\n<\/div>\n<\/div>\n In this case, empty cells will not be ignored.<\/p>\n In most scenarios, we typically use TRUE for the second parameter.<\/p>\n Some might wonder how TEXTJOIN can replace VLOOKUP for matching. First, we can use this formula:<\/p>\n =IF(B:B = E2, C:C, “”) <\/em><\/p>\n<\/div>\n<\/div>\n<\/div>\n This will find the corresponding salary in column C for the name in E2, leaving other cells blank.<\/p>\n By combining both functions, the complete formula becomes:<\/p>\n =TEXTJOIN(“”, TRUE, IF(B:B = E2, C:C, “”)) <\/em><\/p>\n<\/div>\n<\/div>\n<\/div>\n This formula will return the matching results.<\/p>\n Some may argue that this isn\u2019t much more convenient, but it becomes extremely useful in one-to-many scenarios. =TEXTJOIN(“\u3001”, TRUE, IF(A:A = E2, B:B, “”)) <\/em><\/p>\n<\/div>\n<\/div>\n<\/div>\n This formula will connect all the results that meet the condition using a Chinese comma (\u3001), while ignoring empty cells.<\/p>\n Isn\u2019t that powerful? Have you learned this function? Give it a try!<\/p>\n A0005<\/em><\/p>","protected":false},"excerpt":{"rendered":" In the past, when faced with lookup and match problems, […]<\/p>\n","protected":false},"author":1,"featured_media":2443,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[8,7,25,20,18],"tags":[208,90],"class_list":["post-2849","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","category-excel-formulas","category-logical","category-onlinesupport","category-text","tag-textjoin","tag-vlookup"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/06\/Excel-new7.png?fit=2102%2C679&ssl=1","_links":{"self":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/2849","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/comments?post=2849"}],"version-history":[{"count":5,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/2849\/revisions"}],"predecessor-version":[{"id":3160,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/2849\/revisions\/3160"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media\/2443"}],"wp:attachment":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media?parent=2849"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/categories?post=2849"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/tags?post=2849"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}2. Replacing Lookup and Match<\/h4>\n
\nFor example, if we want to find salary data based on employee names, we need to use the IF function in conjunction with TEXTJOIN.<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2024\/09\/A0004-5.png?resize=1024%2C414&ssl=1\")
<\/div>\n![\"\"](\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2024\/09\/A0004-4.png?resize=1024%2C414&ssl=1\")
<\/div>\n3. Powerful One-to-Many Lookup<\/h4>\n
\nFor example, if we want to list all employee information based on department information, we can use the following combination of formulas:<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2024\/09\/A0004-3.png?resize=1024%2C414&ssl=1\")
<\/div>\n