Nested IF statement

W

WIJ

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$127,2),
IF(HOURS!C12=2,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$127,2),
IF(HOURS!C12=4,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$127,2),
IF(HOURS!C12=5,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$127,2),
IF(HOURS!C12=6,VLOOKUP(HOURS!D12,SYSTEMS!$A$2:$B$127,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))))))))
 
J

Jacob Skaria

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!D12,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
 
W

WIJ

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top