Nesting IF

[jamada]

Can someone be kind enough to assist me with this please, I dont know whats wrong with it?

=IF(A2=""+AND(IF(J3="","",IF(A2="FA",$X$3,IF(A2="999","",IF($J3="EMJ",Y3,IF($J3="A319",Y4,IF(J3="A320",Y5,IF(J3="A321",Y6,IF(J3="B767",Y7,IF(J3="B763",Y8,IF(J3="A330",Y9,IF(J3="777L",Y10,IF(J3="777W",Y11,""))))))))))))))

 

[MrKowz]

I see several things wrong with it: too many nests, incorrect syntax, and I also have no idea what it is meant to do (too big for me to really take apart effectively).

Can you please explain exactly what this formula is meant to do?

 

[jamada]

Thanks for the response, I dont think its the right way to go about what I'm trying to achive I just dont know any better!

Basically.

Q3 must be populated with data stored in range W3:Y11.

IF A2 = "FA", and J3 = Validated "EMJ", then Q3 must be populated with data stored in X3.
IF A2 = "SD", and J3 = Validated "EMJ", then Q3 must be populated with data stored in Y3.

IF A2 = "FA", and J3 = Validated "A319", then Q3 must be populated with data stored in X4.
IF A2 = "SD", and J3 = Validated "A319", then Q3 must be populated with data stored in Y4.

IF A2 = "FA", and J3 = Validated "A320", then Q3 must be populated with data stored in X5.
IF A2 = "SD", and J3 = Validated "A320", then Q3 must be populated with data stored in Y5.

Column W is where the validation list is.

This repeats itself throughth evalidation list until row/range Y12.

I hope this helps.... thank you

 

[MrKowz]

Ok, this looks like it is something that we can better handle with an INDEX/MATCH formula.

Try this:

=INDEX($X$3:$Y$11,MATCH(J3,$W$3:$W$11,0),MATCH(A2,{"FA","SD"},0))

 

[jamada]

Mr Kowz, your amazing!

I've been fighting with attempting this since yesterday morning, this is clean and simplified.

I did add $ to A2, A$2, and its fantastic.

I do have one question though, with the formulas extended down the appropriate range within column Q

Is there away Not to display #N/A in column Q, when Cell A$2 and any of the cells in column J have no data?

Thank You

 

[MrKowz]

Try:

=IF(OR(A$2="",J3=""),"",INDEX($X$3:$Y$11,MATCH(J3,$W$3:$W$11,0),MATCH(A2,{"FA","SD"},0)))

Or alternatively if that doesn't work for you:

Excel 2003 or older:
=IF(ISERROR(INDEX($X$3:$Y$11,MATCH(J3,$W$3:$W$11,0),MATCH(A2,{"FA","SD"},0))),"",INDEX($X$3:$Y$11,MATCH(J3,$W$3:$W$11,0),MATCH(A2,{"FA","SD"},0)))

Excel 2007 or newer:
=IFERROR(INDEX($X$3:$Y$11,MATCH(J3,$W$3:$W$11,0),MATCH(A2,{"FA","SD"},0)),"")

 

[jamada]

Its amazing what you can do with someone elses knowledge!

Thanks you very much for the assistance, time and effort on your part, it was/is appreciated!

Graham

 

[MrKowz]

Its amazing what you can do with someone elses knowledge!

Thanks you very much for the assistance, time and effort on your part, it was/is appreciated!

Graham

Glad it works. Thank you for the feedback!