if And

[nirvehex]

Hi,

This code should return the value in AQ10, but for some reason I'm getting a #VALUE error.

=IF(AND(VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE)=VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE)=VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE)=VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE)=VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE),VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE)>=5,VLOOKUP('WOs (DSP Only)'!A:F,6,FALSE)<=7.5),AQ10,"wrong")

All the VLOOKUPS 100% equal 7.2. But at some point it gives me a TRUE=FALSE in the AND statement. I'm trying to test if all the VLOOKUP give equal each other, which they do, but this statement doesn't work. What am I doing wrong?

Thanks!

 

[etaf]

asre you sure its 100% same value - I have seen this type of issue where because of the overflow error in 14 digit numbers you get
7.1999999999999999999999
or
7.2000000000000000001
instead of 7.2
maybe worth just checking a few of the vlookup values and doing just a VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE)=VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE)
see if you get TRUE

OPS - missed the AND()
Thanks
Special-K99

 

[Special-K99]

You cant say IF B=C=D=E=F which is effectively what you have with the VLOOKUPs
You have to say (IF(AND(B=C,B=D,B=E...etc

Try

=IF(AND(VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE)=VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE),VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE)=VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE),VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE)=VLOOKUP(A10,'WOs (DSP Only)'!A:E,5,FALSE),VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE)=VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE),VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE)>=5,VLOOKUP('WOs (DSP Only)'!A:F,6,FALSE)<=7.5),AQ10,"wrong")

 

[Special-K99]

Depending opn your values you might be able to shorten that to

=IF(AND(VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE)+VLOOKUP(A10,'WOs (DSP Only)'!A:C,3,FALSE)+VLOOKUP(A10,'WOs (DSP Only)'!A:D,4,FALSE)+VLOOKUP(A10,'WOs (DSP Only)'!A:E,2,FALSE)+VLOOKUP(A10,'WOs (DSP Only)'!A:F,2,FALSE)=VLOOKUP(A10,'WOs (DSP Only)'!A:B,2,FALSE)*6,VLOOKUP(A10,'WOs (DSP Only)'!A:F,6,FALSE)>=5,VLOOKUP('WOs (DSP Only)'!A:F,6,FALSE)<=7.5),AQ10,"wrong")