Forum Discussion
If formula based on cell value
Not sure if an "IF" formula is the right one needed here, but this is what I am trying to do. In cell B9, I want text to display based on what is in cell I6. I have I6 set up as a list of one of the following AS, EA and PI (acronyms for Assemblies, Each and Per Install)
I am trying to get the worksheet to do the following
- If I6 is blank, I want B9 to read "Select Unit"
- if I6 is AS, I want B9 to read "Assemblies"
- if I6 is EA, I want B9 to read "EACH"
- if I6 is PI, I want B9 to read "Installs"
This is what I started with, but I cannot figure out how to include the rest of the conditions -
=IF(I6="AS","Assemblies","Select Unit")
Any help would be appreciated.
Thanks,
3 Replies
Alternative A – CHOOSE + MATCH (works in all Excel versions)
This approach uses MATCH to find the position of the value in I6 within a fixed list of unit codes. CHOOSE then returns the corresponding description.
=IFERROR(CHOOSE(MATCH(I6,{"AS","EA","PI"},0),"Assemblies","EACH","Installs"),"Select Unit")
Logic:
- AS → Assemblies
- EA → EACH
- PI → Installs
- Blank or any other value → Select Unit
Alternative B – XLOOKUP with inline arrays (Excel 2021 / Microsoft 365 only)
This version avoids helper columns by using two inline arrays: one for the lookup values and one for the returned results.
=XLOOKUP(I6,{"AS","EA","PI"},{"Assemblies","EACH","Installs"},"Select Unit")
Logic:
- Searches for the value in I6
- Returns the matching description
- If no match is found (including blank), returns "Select Unit"
*For future expansion (adding more unit types later), XLOOKUP is usually the easiest to maintain. For maximum backward compatibility, use CHOOSE + MATCH.
neilm1 Alternatively, try this:
=SWITCH(I6,"AS", "Assemblies","EA", "EACH", "PI", "Installs", "Enter Unit")Perhaps...
=IF(I6="","Select Unit", IF(I6="AS","Assemblies", IF(I6="EA","EACH", IF(I6="PI","Installs",""))))
