Nested IF statement
 

Hi all,

This is the first time I have posted on here.. I am trying to make up the
following nested IF staement (I am drawing alot of information from a
database). It all works for me exept for the last nested If statement
line(Administration). I dont know why this is as I have followed the same
procedues as the other lines...I not sure if this makes sense or not.

=IF(HOURS!C12=0,VLOOKUP(HOURS!D12,PROJECTS!$A$2:$B $25,2),
IF(HOURS!C12=1,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=2,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=4,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=5,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=6,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=7,VLOOKUP(HOURS!D12,MajorTasks!$A$2:$ B$3,2),
IF(HOURS!C12=8,VLOOKUP(HOURS!D19,ADMINISTRATION!$A $2:$B$10,2,4),
NOT IN DATABASE))))))))

Nested IF statement
 

The 4th argument of VLOOKUP should be 0 or 1..instead of 4...
VLOOKUP(HOURS!D19,ADMINISTRATION!$A$2:$B$10,2,4)

You can shorten that as below

=IF(HOURS!C12=0,VLOOKUP(HOURS!D12,PROJECTS!$A$2:$B $25,2,0),
IF(AND(HOURS!C12=1,HOURS!C12<=6),VLOOKUP(HOURS!D1 2,SYSTEMS!$A$2:$B$127,2,0),
IF(HOURS!C12=7,VLOOKUP(HOURS!D12,MajorTasks!$A$2:$ B$3,2,0),
IF(HOURS!C12=8,VLOOKUP(HOURS!D19,ADMINISTRATION!$A $2:$B$10,2,0),"NOT IN
DATABASE"))))

If this post helps click Yes
---------------
Jacob Skaria


"WIJ" wrote:

Hi all,

This is the first time I have posted on here.. I am trying to make up the
following nested IF staement (I am drawing alot of information from a
database). It all works for me exept for the last nested If statement
line(Administration). I dont know why this is as I have followed the same
procedues as the other lines...I not sure if this makes sense or not.

=IF(HOURS!C12=0,VLOOKUP(HOURS!D12,PROJECTS!$A$2:$B $25,2),
IF(HOURS!C12=1,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=2,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=4,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=5,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=6,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=7,VLOOKUP(HOURS!D12,MajorTasks!$A$2:$ B$3,2),
IF(HOURS!C12=8,VLOOKUP(HOURS!D19,ADMINISTRATION!$A $2:$B$10,2,4),
NOT IN DATABASE))))))))

Nested IF statement
 

I notice you're missing "IF(HOURS!C12)=3".

Is that intentional?

--
Biff
Microsoft Excel MVP


"WIJ" wrote in message
...
Hi all,

This is the first time I have posted on here.. I am trying to make up the
following nested IF staement (I am drawing alot of information from a
database). It all works for me exept for the last nested If statement
line(Administration). I dont know why this is as I have followed the same
procedues as the other lines...I not sure if this makes sense or not.

=IF(HOURS!C12=0,VLOOKUP(HOURS!D12,PROJECTS!$A$2:$B $25,2),
IF(HOURS!C12=1,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=2,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=4,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=5,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=6,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=7,VLOOKUP(HOURS!D12,MajorTasks!$A$2:$ B$3,2),
IF(HOURS!C12=8,VLOOKUP(HOURS!D19,ADMINISTRATION!$A $2:$B$10,2,4),
NOT IN DATABASE))))))))

Nested IF statement
 

Thanks for your answers, Biff it worked I just had to change
"VLOOKUP(HOURS!D19,ADMINISTRATION!$A$2:$B$10,2,4), " to
"VLOOKUP(HOURS!D12,ADMINISTRATION!$A$2:$B$10,2,4), . (My mistake to start with)

And T. Valko, I have delibratly missed "IF(HOURS!C12)=3". thanks for picking
it up though.

WIJ


"T. Valko" wrote:

I notice you're missing "IF(HOURS!C12)=3".

Is that intentional?

--
Biff
Microsoft Excel MVP


"WIJ" wrote in message
...
Hi all,

This is the first time I have posted on here.. I am trying to make up the
following nested IF staement (I am drawing alot of information from a
database). It all works for me exept for the last nested If statement
line(Administration). I dont know why this is as I have followed the same
procedues as the other lines...I not sure if this makes sense or not.

=IF(HOURS!C12=0,VLOOKUP(HOURS!D12,PROJECTS!$A$2:$B $25,2),
IF(HOURS!C12=1,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=2,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=4,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=5,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=6,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$1 27,2),
IF(HOURS!C12=7,VLOOKUP(HOURS!D12,MajorTasks!$A$2:$ B$3,2),
IF(HOURS!C12=8,VLOOKUP(HOURS!D19,ADMINISTRATION!$A $2:$B$10,2,4),
NOT IN DATABASE))))))))




.