Multiple IF Statements with Three Variables

[Gemsera]

Hi All,

After a thorough search I couldnt see anything related or similar, so
unfortunately I have to ask the question.

I have three variables I need to calculate:

Software Build
Hardware Type
Project Phase

The spreadsheet is a list of faults reported on the software. The goal is to
list in the table which phase each fault is for, so we can do a total. I have
done the preliminary list which is similar to this (for each hardware,
software and phase):

IF I1 = Hardware-EMS AND J1 = V07.01.00S00 OR J1 = V07.01.00S101
THEN print Phase 1

thats the basics of what I require, but I require it for 14 different
combinations.

I really do hope someone can help, as this would make such a difference to
our reporting. Thank you in advance for any help at all.

x

 

[Pete_UK]

Perhaps you can post your preliminary list of the 14 conditions in
full ?

It strikes me that you can build up a table of these combinations and
then use a lookup formula to derive the phase from it, rather than
multiple IFs, but will need to see what other combinations you might
have.

Pete

 

[Gemsera]

Thanks for such a quick response!

Here is the preliminary list:

IF I1 = Hardware1 AND J1 = V07.01.00S00 OR J1 = V07.01.00S101
THEN print Phase 1

IF I1 = Hardware1 AND J1 = V07.01.00R000P3A004
THEN print Phase 2



IF I1 = Hardware2 AND J1 = V07.01.00S106 OR J1 = V07.01.00S107
THEN print Phase 1

IF I1 = Hardware2 AND J1 = V07.01.00S201 OR J1 = V07.01.00S202 OR J1 =
V07.01.00A208
THEN print Phase 2



IF I1 = Hardware3 AND J1 = V07.01.00S106 OR J1 = V07.01.00S107
THEN print Phase 1

IF I1 = Hardware3 AND J1 = V07.01.00S202 OR J1 = V07.01.00A208
THEN print Phase 2


IF I1 = Hardware4 AND J1 = V07.01.00R001
THEN print Phase 1

IF I1 = Hardware4 AND J1 = V07.01.00A004 OR J1 = V07.01.00A007
THEN print Phase 2



IF I1 = Hardware5 AND J1 = 4.1
THEN print Phase 1

IF I1 = Hardware5 AND J1 = 4.2
THEN print Phase 2


IF I1 = Hardware6 AND J1 = V07.01.00R00
THEN print General Phase



IF I1 = Hardware7 AND J1 = V07.01.00R01
THEN print Phase 1

IF I1 = Hardware7 AND J1 = V07.01.01A015 OR J1 = V07.01.01A020
THEN print Phase 2


IF I1 = Hardware8 AND J1 = V07.01.00S101
THEN print Phase 1

I have never used vlookup, so would appreciate advice

 

[Spiky]

Thanks for such a quick response!

Here is the preliminary list:

IF I1 = Hardware1 AND J1 = V07.01.00S00 OR J1 = V07.01.00S101
THEN print Phase 1

IF I1 = Hardware1 AND J1 = V07.01.00R000P3A004
THEN print Phase 2

IF I1 = Hardware2 AND J1 = V07.01.00S106 OR J1 = V07.01.00S107
THEN print Phase 1

IF I1 = Hardware2 AND J1 = V07.01.00S201 OR J1 = V07.01.00S202 OR J1 =
V07.01.00A208
THEN print Phase 2

IF I1 = Hardware3 AND J1 = V07.01.00S106 OR J1 = V07.01.00S107
THEN print Phase 1

IF I1 = Hardware3 AND J1 = V07.01.00S202 OR J1 = V07.01.00A208
THEN print Phase 2

IF I1 = Hardware4 AND J1 = V07.01.00R001
THEN print Phase 1

IF I1 = Hardware4 AND J1 = V07.01.00A004 OR J1 = V07.01.00A007
THEN print Phase 2

IF I1 = Hardware5 AND J1 = 4.1
THEN print Phase 1

IF I1 = Hardware5 AND J1 = 4.2
THEN print Phase 2

IF I1 = Hardware6 AND J1 = V07.01.00R00
THEN print General Phase

IF I1 = Hardware7 AND J1 = V07.01.00R01
THEN print Phase 1

IF I1 = Hardware7 AND J1 = V07.01.01A015 OR J1 = V07.01.01A020
THEN print Phase 2

IF I1 = Hardware8 AND J1 = V07.01.00S101
THEN print Phase 1

I have never used vlookup, so would appreciate advice

It looks to me like you actually have 22 hardware/software
combinations listed there. I would make a simple database with 2
columns in a separate sheet. Assume in A1:B22 on Sheet2. Put every
combination in the first column like this, just run together with no
space between:
Hardware1V07.01.00S00
Hardware1V07.01.00S101
Hardware2V07.01.00S106
Etc.

Then the 2nd column is the appropriate Phase for each.

Then your VLOOKUP is this:
=VLOOKUP(I1&J1,Sheet2!$A$1:$B$22,2,FALSE)

Uh, don't make any typos.

 

[Gemsera]

It is a little more complicated by the fact that the XML isnt formatted in
that nature when the reports are done, but I can do that manually.

Thank you for the enlightenment I desperately needed!