CODEWITHSUNDEEP
excelexcel-2010
krebshack
krebshack

Reputation: 1072

#VALUE! when using multiple IF statements

I'm working on a spreadsheet and what I'd like to do is have the value a user selects from a drop down menu auto populate, about, twelve other cells.

I have the drop down menu done. It has the following values:

Contract Specific/Lot Tracked #1

Contract Specific/Lot Tracked #2

Stores Direct

GFM/GFE #1

GFM/GFE #2

The values that depend on the drop down menu are pretty varied depending on the cell but it's always going to be a text string. Which is why in the example below you see "Order Based" and "Not Lot Tracking".

What I need is something like this:

=IF(B36="Contract Specific/Lot Tracked #1","Order Based",IF(B36="Contract Specific/Lot Tracked #2","Order Based"),IF(B36="Stores Direct","Not Lot Tracking"),IF(B36="GFM/GFE #1","Order Based"),IF(B36="GFM/GFE #2","Order Based")

Basically, if B36 is equal to one of six values, then input this.

I've got this formula to work:

=IF(B36="Contract Specific/Lot Tracked #1","Not Serial Tracking",IF(B36="Contract Specific/Lot Tracked #2","Serial Tracking"))

But it only works for two values and it doesn't expand to three values. I also tried working with the OR statement but I'm not having luck there either.

Upvotes: 0

Views: 22

Answers (1)

kolcinx
kolcinx

Reputation: 2233

What you need is something like this (I reordered the parenthesis):

=IF(B36="Contract Specific/Lot Tracked #1","Order Based",IF(B36="Contract Specific/Lot Tracked #2","Order Based",IF(B36="Stores Direct","Not Lot Tracking",IF(B36="GFM/GFE #1","Order Based",IF(B36="GFM/GFE #2","Order Based")))))

If there will be much more combinations, it's impractical to create such long nested if statements. Consider creating a table with pairs of strings, and just use Vlookup instead.

Upvotes: 1

Related Questions