Forum Discussion
If statements with multiple conditions
Hello!
I'd like to manage an If formula to allow multiple text indicators.
I have a list of cities in on column -- LA, SF, NY, Chicago, etc. I then want to create columns for each city to the right with an X to indicate if that city name showed up in that original cell.
I have the basic formula as "=IF(F3="LA", "X") which works fine if the only city listed in that original formula was LA but then if I have multiple cities the cell reads "FALSE."
what is the formula to list an X in Each corresponding column to indicate if the citys name was in that original column?
If you have the flexibility to name cities with the same abbreviation that is in your header, then it's a fairly simple formula: =IFERROR(IF(FIND(G$2,$F3)>0,"X"),"")
That formula can be copied to every cell in that table and work. But it works when the values in the cell in column F look like this, not when you have the abbreviation in the heading (BOS) but the full name spelled out ("Boston") in the cell being evaluated.
OR you could spell the name out fully in each case and that too would work. What you need is consistency.
You and I with our more flexible brains are able to know DFW means Dallas/Fort Worth, and a formula could be written to let Excel recognize it as well, or perhaps a table created to for the formula to refer to.
I guess the question to you is how super sophisticated (i.e., flexible) you want the solution to be.
10 Replies
If you have the flexibility to name cities with the same abbreviation that is in your header, then it's a fairly simple formula: =IFERROR(IF(FIND(G$2,$F3)>0,"X"),"")
That formula can be copied to every cell in that table and work. But it works when the values in the cell in column F look like this, not when you have the abbreviation in the heading (BOS) but the full name spelled out ("Boston") in the cell being evaluated.
OR you could spell the name out fully in each case and that too would work. What you need is consistency.
You and I with our more flexible brains are able to know DFW means Dallas/Fort Worth, and a formula could be written to let Excel recognize it as well, or perhaps a table created to for the formula to refer to.
I guess the question to you is how super sophisticated (i.e., flexible) you want the solution to be.
GlynWilliams See attached. Note that you need to use the same abbreviations in F as the ones you have in row 2.
Riny_van_Eekelen This is great thank you so much! This saves my overworked staff member a lot of time!
I think that the answer you got from @Riny_van_Eekelen is a bit more elegant than mine; one of the delightful aspects of Excel is that there are almost always multiple ways to get to a given solution for any given problem. In this case, as Sergei and I both suggested, you could also have an elaborate table that connected the airport codes (or other abbreviations) with spelled out names. The potential problem with that is that spelling still matters, and humans are not always reliable in their speling.
The only question is how Excel will know that NY and NYC; Boston and BOS, etc are the same names? That could be mapping table, or will use same names everywhere, what is the logic?
SergeiBaklan good question, I would ensure the statement column is consistent.
When it could be
=IF(COUNTIF($F3, "*"&G$2&",*")+COUNTIF($F3, "* "&G$2&"*")+($F3=G$2),"X","")as
Different conditions are to be sure we check separate words (with comma after OR with space before OR only this word), not part of other words
