Using IF, AND, OR and VLOOKUP in one

[bat18]

Hi,

Here is my problem I am trying to write

'if the value in E1 is anywhere in column T AND either R1 OR S1 is in column U then TRUE, otherwise FALSE'

Now using vlookups to get the values works fine, also when i combine the IF statement with AND it works but when i try

=IF(AND(VLOOKUP(....., OR(VLOOKUP....

it just wont work. Any help on this would be greatly appreciated

Thanks

 

[Special-K99]

If its an array formula you're using I had this problem

Try using

IF(condition1,IF(condition2,TRUE,FALSE),FALSE)

rather than

IF(AND(condition1, condition2),TRUE,FALSE)

 

[bat18]

I have worked out that the problem is within the OR(VLOOKUP... bit. One of the two criteria will work and one will always not i just need that atleast one of them hold but as soon as one of them fail the whole formula fails. If none of this makes sense i will send the actual formulas being used.

Thanks

 

[bat18]

This works fine

=IF(AND(VLOOKUP($E2, $U$1:$U$30000, 1, FALSE), VLOOKUP($S1, $V$1:$V$30000, 1, FALSE )),TRUE, FALSE)

As soon as i try and get another condition in there it fails

=IF(AND(VLOOKUP($E2, $U$1:$U$30000, 1, FALSE), OR(VLOOKUP($R1, $V$1:$V$30000, 1, FALSE), VLOOKUP($S1, $V$1:$V$30000, 1, FALSE ))),TRUE, FALSE)

Special K would i need to use three if statements in this case??

Thanks again

 

[xanksx]

Try the below,
Replace "TRUE" and "FALSE" (at the end) with whatever you want

=IF(AND(ISERROR(VLOOKUP(E1,T:T,1,0))=FALSE,OR(ISERROR(VLOOKUP(R1,U:U,1,0))=FALSE,ISERROR(VLOOKUP(S1,U:U,1,0))=FALSE)),"TRUE","FALSE")

 

[WaterGypsy]

Hi

If I could drop in my two pence worth - in this situation I avoid using VLOOKUP cos I get error conditions if the values aren't found.... I'd use COUNTIF ....e.g.

=if(and(countif(e1,T:T)>0,or(countif(r1,T:T)>0,countif(s1,T:T)>0)),T,F)

but then maybe I shouldn't be getting the errors with VLOOKUP

Kevin

 

[dcamos]

The problem with the OR and the vlookup is that you don't get a true/false result from the formula. You get an error or a number. You should be using isnumber(match() to get a true/false result, which will allow the OR to properly calculate.

 

[bat18]

Yes absolutely right dcamos and that is definitely why xankx's formula worked perfectly for me. Cheers to all the above!!

 

[bat18]

Just adding to this thread,

I am having trouble matching values that are obtained from other formulas. I really don't want to have to copy and paste values all the time and would like to know if anyone knows what i'm doing wrong? or if there is a formula to pick up values

 

[dcamos]

It depends on what the formula is bringing back. You might not exactly be matching what you are looking for, a little more info would be helpful.