IF, AND, VLOOKUP help

AESP920

New Member
Joined
Feb 7, 2005
Messages
48
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
 

Some videos you may like

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
Joined
May 4, 2015
Messages
810
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))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,095,745
Messages
5,446,251
Members
405,392
Latest member
Steveoaktree1977

This Week's Hot Topics

Top