IF function

Occasional Visitor

I'm looking to create an equation to fill in for an exported document created monthly. When exporting into Excel, one column will show numeral values EX. ^13^, ^14^, etc. In place of the numeral value I'm looking for a specific text to display depending on the value entered. 

 

IF ^13^= Atrophy

IF ^14^= Organisms(s) (see comment):

 

I have 17 different text options that could populate. Additionally, it's possible for more than one numeral value to appear in one box and would like all texts to appear.

 

I have been tying the "IF Equations" and am not getting a text response. Any suggestions?

1 Reply

Hi @SWALLAC4 

IF will be very long and complex to maintain. XLOOKUP will solve the core problem much easier using a lookup table with the numeral and the text.

"Lookup the input in column Numeral and return column Text";

bosinander_0-1646460197986.png

If there are few and predictable combination of numeral values, you may add them to the table like a new row at F4;

^13^,^14^Atrophy, Organisms(s) (see comment)

Assuming though that the combinations are unpredictable, the input has to be separated, xlookuped and then joined.

The formula can be quite long, complex and using functions that as of today is available only in Excel 365;

=BYROW(A30:A32;LAMBDA(range;TEXTJOIN(", ";1;XLOOKUP(FILTERXML("<tr><td>" & SUBSTITUTE(range;",";"</td><td>") & "</td></tr>";"//td");Table1[Numeral];Table1[Text]))))

The attached file has it broken up in a few more steps that makes it easier to follow and play with.

bosinander_1-1646460622195.png

=BYROW(A9:A11;LAMBDA(range;
LET(
delimiterToXML;SUBSTITUTE(range;",";"</td><td>");
asXML;"<tr><td>" & delimiterToXML & "</td></tr>";
asSeparatevalues;FILTERXML(asXML;"//td");
lookupTexts;XLOOKUP(asSeparatevalues;Table1[Numeral];Table1[Text]);
output;TEXTJOIN(", ";1;lookupTexts);
output
)
))

Note that my national settings use semicolon as separator so you may have to change to comma if you do not want to use the attached file.