Trying to use IF statement to return output combining string and cell information

Question

I am trying to write an IF statement, where if column F = "MOPAR" I want to return column E with "FCA-" to create "FCA-60519" or if column F = "SAFELITE", then I want to return column E with "SGI-05188"

I was trying to use TextJoin, but was just getting errors.

I have tried using TEXTJOIN, but only getting errors.

Answer 1

Assuming the data you're checking is in F2, and the data you're pulling is in E2, no need to overcomplicate. You can even create a table where you say MOPAR in one column and the prefix you want to pull and start with that process in case there's more than 2 cases.

However, if you're only checking for those 2 rules, you can populate the prefix then combine it with whatever is in E2.

=if( F2="MOPAR", "FCA-", IF( F2 = "SAFELITE", "SGI-") ) & E2

If it's only the 2 rules, you can further simplify by only checking for one of the examples. However, this will see a non-match for "MOPAR" and always put "SGI-", so you have to be mindful of what other data appears in your dataset

=IF( F2 = "MOPAR", "FCA-", "SGI-") & E2

I like to setup a table like below, because as more datapoints are introduced you can add them and easily maintain them without adjusting formulas

Code	Prefix
MOPAR	FCA-
SAFELITE	SGI-

and you can name this by using Ctrl + T and name it something like _Labels and do a xlookup or vlookup to pull the prefix then combine it with the value in col e using the & operator.

=vlookup( F2, _Labels, 2, 0) & E2

Answer 2

Assuming you want to return value in column D?

=IF(F1="MOPAR","FCA-" & E1, IF(F1="SAFELITE","SGI-" & E1,""))

What have you tried so far? Probably should work with TEXTJOIN, but I currently don't have acces to Excel, so can't test it out myself.