# IF, AND, VLOOKUP help

#### AESP920

##### New Member
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.

Aesp

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Chrisdontm

##### Well-known Member
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))),"")

This worked.

Thank you!
A