EXCEL - IF formula based on cell value or entry

Question

EXCEL - IF formula based on cell value or entry

Neil Monkman 20

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,

0 comments

Answer accepted by question author

Kai-L 19,050 Microsoft External Staff Moderator

Dear Neil,

Good day, and thank you for reaching out to the Q&A Forum. You are on the right track, this can be accomplished using either a nested IF formula or the SWITCH function. Here are both options for you:

Option 1: Nested IF

Enter the following formula in cell B9:

=IF(I6="","Select Unit",IF(I6="AS","Assemblies",IF(I6="EA","EACH",IF(I6="PI","Installs","Select Unit"))))

The formula checks the value in I6 in this order:

Blank > Select Unit
AS > Assemblies
EA > EACH
PI > Installs
Anything unexpected > Select Unit

Excel supports nested IF functions, though long nested formulas can become harder to maintain over time.

Option 2: SWITCH

If your version of Excel supports the SWITCH function, this version is easier to read:

=SWITCH(I6,"AS","Assemblies","EA","EACH","PI","Installs","Select Unit")

SWITCH compares the value in I6 against each listed acronym and returns the corresponding description. The final "Select Unit" is the default result, so it covers both a blank cell and any value not listed.

I hope this helps. Please let me know if you need anything else.

If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Neil Monkman 20 Reputation points

2026-07-23T20:21:46.9833333+00:00

Thank you SO MUCH!!!!
Kai-L 19,050 Reputation points Microsoft External Staff Moderator

2026-07-23T20:51:27.7933333+00:00

Dear Neil,

Thank you for your update. I’m glad I could provide helpful information to resolve this issue. If you have any further questions or need additional assistance down the road, feel free to post back. Not only I, but other professionals in the forum are always here to help. Thank you again for being a valued member of the Q&A community and I wish you all the best!

3 additional answers

Your answer

Neil Monkman 20 Reputation points

2026-07-23T20:21:46.9833333+00:00

Thank you SO MUCH!!!!
Kai-L 19,050 Reputation points Microsoft External Staff Moderator

2026-07-23T20:51:27.7933333+00:00

Dear Neil,

Thank you for your update. I’m glad I could provide helpful information to resolve this issue. If you have any further questions or need additional assistance down the road, feel free to post back. Not only I, but other professionals in the forum are always here to help. Thank you again for being a valued member of the Q&A community and I wish you all the best!

Answer 1

IlirU 2,651 Volunteer Moderator

User's image

You can use this formula in cell B9 (see the screenshot above):

=FILTER(B2:B5, A2:A5 = I6)

Hope this helps.

0 comments

Answer 2

Ashish Mathur 102.4K Volunteer Moderator

Hi,

In cell B9, enter this formula

=xlookup(I6,$A$1;$A$3,$B$1:$B$3,"Select unit")

A1:A3 has the abbreviations. B1:B3 has the full form.

Hope this helps.

0 comments

Answer 3

You can do this with a nested IF formula. For your four conditions, use:

=IF(I6="","Select Unit",IF(I6="AS","Assemblies",IF(I6="EA","EACH",IF(I6="PI","Installs","Select Unit"))))

This formula checks whether I6 is blank first. If it is, B9 displays Select Unit. If not, it checks for AS, then EA, then PI. If I6 contains anything other than those values, it also returns Select Unit.

If you have Microsoft 365, a cleaner approach is to use the SWITCH function:

=SWITCH(I6,"","Select Unit","AS","Assemblies","EA","EACH","PI","Installs","Select Unit")

SWITCH is easier to read and maintain than multiple nested IF statements, making it the preferred option for this type of lookup.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin