Appreciation post: I asked a question here about a weird Excel formula and someone's fix worked in 10 minutes.

I was trying to combine text from two cells and kept getting an error for hours. A user named 'data_guy' suggested using TEXTJOIN instead, and it just clicked. Anyone know other common formula traps like that?

3 comments

3 Comments

willowh202mo ago

Totally get the TEXTJOIN love, but I actually find it can be overkill for simple joins. I still use the old ampersand for two cells, like =A1&" "&B1. It's one less function to remember. The real trap for me is VLOOKUP versus XLOOKUP. People stick with VLOOKUP because it's familiar, but XLOOKUP is so much simpler and doesn't break if you insert a column. Another one is using SUMIFS correctly, people mess up the range sizes and get errors.

young.nora2mo ago

Ever tried INDEX/MATCH instead?

-2

robert_jenkins822mo ago

You're right about XLOOKUP being better than VLOOKUP, but I'd push back on the ampersand being simpler. For two cells, sure, but if you need to add a comma or handle empty cells, TEXTJOIN with the ignore empty argument is cleaner. An ampersand chain gets messy fast, like =A1&", "&B1&" "&C1, and fails if B1 is blank. TEXTJOIN just handles it.