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 ...
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.