IF OR with Vlookup

[oz67]

Hi,

I am using three conditions (highlighted in bold) in an IF OR statement below, but I am getting and #N/A. The first condition is true, the other two are false. It should still return "V3". What am I doing wrong?

=IF(OR(VLOOKUP(H4982,'Bob''s V3'!K:K,1,0)=H4982, VLOOKUP(H4982,MFG!A:A,1,0)=H4982, RIGHT(H4982,5)>="17000"),"V3","")

Thanks!

 

[Caleeco]

When a VLOOKUP cannot find a matching value it will return #N/A.

So if your first VLOOKUP finds something, and the other 1 doesn't..... you have an Error in your formula (#N/A) so the whole formula cannot resolve.

To get around this, you can check does the VLOOKUP return the value TYPE you are expecting. So if the VLOOKUP returns a number, try this modifcaiton:

=IF(OR(isnumber(VLOOKUP(H4982,'Bob''s V3'!K:K,1,0)=H4982), isnumber(VLOOKUP(H4982,MFG!A:A,1,0)=H4982), RIGHT(H4982,5)>="17000"),"V3","")

if it returns text, switch this for ISTEXT etc

Cheers
Caleeco

 

[oz67]

Thanks for the reply. I tried both isnumber and istext, but I get "". The first condition is true so it should return V3.

 

[Fluff]

How about
=IF(OR(IFERROR(VLOOKUP(H4982,'Bob''s V3'!K:K,1,0),0)=H4982, IFERROR(VLOOKUP(H4982,MFG!A:A,1,0),0)=H4982, RIGHT(H4982,5)>="17000"),"V3","")

 

[Haseeb Avarakkan]

Try this version:

=IF(OR(COUNTIF('Bob''s V3'!K:K,H4982),COUNTIF(MFG!A:A,H4982),RIGHT(H4982,5)+0>=17000),"V3","")

 

[sykes]

I don't suppose 'Bob''s V3'! ....
...should be 'Bob's V3'! ...
...should it, by any chance?

 

[Fluff]

You need to double-up the apostrophe so Bob''s is correct.

 

[sykes]

Ah, OK. Thanks @Fluff.

 

[oz67]

Guys thanks so much! Both Fluff's and Haseeb's formulas work!

 

[Fluff]

Glad we could help & thanks for the feedback.