Show blank if "x" or "X", else run a VLOOKUP

[rsbidinger]

Hello, I'm trying to put in a formula to output the cell blank if marked 'x' or 'X', else run a VLOOKUP.

How do I do this? Essentially, I'm trying to combine the following...

Here's my IF statement:
=IF(OR(H12<>"X",H12<>"x"),result,"")

Here's my VLOOKUP:
=VLOOKUP($C11,'Blue'!$C11:$O$500,7,FALSE)

Thank you for your help.

Ryan

 

[tyija1995]

I assume result will be the vlookup formula?

Try:
=IF(H12="x","",VLOOKUP($C11,'Blue'!$C11:$O$500,7,FALSE))

IF is not case sensitive so x is equivalent to X.

 

[kweaver]

If your result is the VLOOKUP, just place that in place of "result" as:

=IF(OR(H12<>"X",H12<>"x"),VLOOKUP($C11,$C11:$O$500,7,FALSE),"")

Assuming that's what you mean.

If you want an exact match (e.g., H12 compared to an upper case X):

=IF(EXACT(H12,"X"),VLOOKUP($C11,$C11:$O$500,7,FALSE),"")

 

[Misca]

Welcome to the message board!

Excel isn't too picky about letter casing so you don't have to worry about the "x" or "X" part. That being said all you have to do is combine your two formulas:

=IF(H12="x","",VLOOKUP($C12,Blue!$C11:$O$500,7,FALSE))

 

[Fluff]

Shouldn't that be AND rather than OR in the formula?
Otherwise it will always result in TRUE

 

[rsbidinger]

Thank you so much!! This worked although the VLOOKUP displays 0 instead of the text displayed on the 'Blue' source sheet. Is there a way to show the text instead of a value?

 

[tyija1995]

Shouldn't that be AND rather than OR in the formula?
Otherwise it will always result in TRUE

In the specific case of "x" and "X" it can return FALSE when x (or X) is entered in the cell - as it's the same

 

[tyija1995]

Thank you so much!! This worked although the VLOOKUP displays 0 instead of the text displayed on the 'Blue' source sheet. Is there a way to show the text instead of a value?

I assume this formula is being entered on a different sheet to the "Blue" sheet?
It depends on your data I suppose, hard to tell what it should return!

 

[rsbidinger]

Disregard. Got it working. Thank you again!!!

Thank you so much!! This worked although the VLOOKUP displays 0 instead of the text displayed on the 'Blue' source sheet. Is there a way to show the text instead of a value?

 

[rsbidinger]

Okay one more question...

It's showing zero because the VLOOKUP sees a blank cell. If it's a blank cell, is there a way to tell it to show blank instead of zero? Thanks.