IF(OR does not work.

[oz67]

Hi

When one of the three conditions is true, which returns a "PM12345", I get a blank cell "". I also used ISTEXT, but did not help. How can I get around this?

=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","")

Thanks,

 

[lrobbo314]

You just had some parenthesis out of order.

=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","")

 

[Dave Patton]

Check the syntax in each part. You can use Excel's Formula Evaluate.

T202008a.xlsm
	E
3	V3
4c

Cell Formulas
Range	Formula
E3	E3	=IF(OR(VLOOKUP(H4,'Bob''''s V3'!K:K,1,0)=H4,VLOOKUP(H4,MFG!A:A,1,0)=H4),"V3","")

N.B.
- I used H4 instead of H4982
- you cannot use ' in sheet names
- what do you want to do with the 3rd part of the or? What is the data field.
- what result do you want, text or the value in cell V3?

 

[fhqwgads]

ISNUMBER(VLOOKUP(H4982,'Bob''s V3'!K:K,1,0)=H4982)

i dont think you need the ISNUMBER? the (VLOOKUP(H4982,'Bob''s V3'!K:K,1,0)=H4982) should give you TRUE/FALSE. i dont think you can ISNUMBER or ISTEXT on a TRUE/FALSE or it will always give you FALSE

 

[AhoyNC]

Maybe:

=IF(OR(VLOOKUP(H4982,'Bob''s V3'!K:K,1,0)=H4982,VLOOKUP(H4982,MFG!A:A,1,0)=H4982,RIGHT(H4982,5)*1>=17000),"V3","") If V3 is reference to a cell then drop the quotes around it.

 

[oz67]

Check the syntax in each part. You can use Excel's Formula Evaluate.

T202008a.xlsm
	E
3	V3
4c

Cell Formulas
Range	Formula
E3	E3	=IF(OR(VLOOKUP(H4,'Bob''''s V3'!K:K,1,0)=H4,VLOOKUP(H4,MFG!A:A,1,0)=H4),"V3","")

N.B.
- I used H4 instead of H4982
- you cannot use ' in sheet names
- what do you want to do with the 3rd part of the or? What is the data field.
- what result do you want, text or the value in cell V3?

If you look at the image below, condition 1 returns the value, condition 2 returns #N/A, condition 3 returns the value. Since I am using OR, it should return "V3" since one of the conditions is true, but it returns #N/A. How can I get around #N/A?

 

[AhoyNC]

It's not finding a match which is why you are getting #N/A.
Maybe try changing the 2nd condition to:

IFERROR(VLOOKUP(H4982,MFG!A:A,1,0),0)=H4982