IF, AND, VLOOKUP help

[AESP920]

Hello,

I am trying to do a If And with a vlookup within the same workbook.

IF(AND(IF(AF3431="Red",VLOOKUP(AG3431,'VLookup Table'!E:F,2,FALSE),IF(AF3431="Blue",VLOOKUP(AG3431,'VLookup Table'!E:F,2,FALSE),IF(AF3431="Green",VLOOKUP(AG3431,'VLookup Table'!E:F,2,FALSE),IF(AF3431="Yellow",VLOOKUP(AG3431,'VLookup Table'!E:F,2,FALSE),VLOOKUP(AF3431,'VLookup Table'!D:F,3,FALSE)))))="Other",AQ3431="500"),"Other Colors",IF(AF3431="Red",VLOOKUP(AG3431,'VLookup Table'!E:F,2,FALSE),IF(AF3431="Blue",VLOOKUP(AG3431,'VLookup Table'!E:F,2,FALSE),IF(AF3431="Green",VLOOKUP(AF3431="Yellow",VLOOKUP(AG3431,'VLookup Table'!E:F,2,FALSE),VLOOKUP(AF3431,'VLookup Table'!D:F,3,FALSE))))))

At the end of the day, if there is red, blue, green, or yellow in column AF, then I need to vlookup in a separate tab to columns E-F. But if there are none of these colors in column AF, then just vlookup D-F in the same Lookup table. Also if there is the word "Other" in AF but it has a 500 code in AQ, then I want it to say Other Colors.
Its giving me a #Value ! at the moment.

Thanks in advance,
Aesp

 

[Chrisdontm]

If I understand you correctly, replace your formula with this;

=IFERROR(IF(AND(AF3431="Other",AQ3431=500),"Other Colors",IF(OR(AF3431="red",AF3431="blue",AF3431="green",AF3431="yellow"),VLOOKUP(AG3431,'VLookup Table'!E:F,2,0),VLOOKUP(AF3431,'VLookup Table'!D:F,3,0))),"")

 

[AESP920]

This worked.

Thank you!
A