Handling many conditions criteria for a VLOOKUP?

[sbawnh]

Hi,
If my Vlookup returns values that I don't want, I want to process them. What's an elegant way of doing this?

Vlookup(value,range,col,0) = X
IF X = "", ""
IF X = #N/A, ""
IF X = 0, ""
IF X > 0, ""

 

[Panda514]

Hello,

Here is one way to do it. Just use the OR function and repeat for as many returns as you want to change to blank.

=IFERROR(IF(OR(VLOOKUP(VALUE,RANGE,COL,FALSE)="",VLOOKUP(VALUE,RANGE,COL,FALSE)="#NA",VLOOKUP(VALUE,RANGE,COL,FALSE)=0,VLOOKUP(VALUE,RANGE,COL,FALSE)>0),"",VALUE,RANGE,COL,FALSE)),"")

 

[sbawnh]

Thank you. I tried to edit my post to acknowledge the repeating vlookups!
I'm hoping instead there's something neat like this.

If(vlookup(value,range,col,false)OR(=0,="",=">"",="#N/A"),"",vlookup(value,range,col,false))

 

[Panda514]

I'm not aware of being able to use the OR function without writing out each condition

 

[sbawnh]

Thanks for the help. I've been searching the webs for options possibly using =Concatenate, but without a Third-party plugin, it seems there's no option.

I'll mark your response as a solution since it's correct. Thanks again.

 

[Fluff]

You can write it like
=IF(OR(VLOOKUP(L1,B2:J31,5,0)={0,1,2,3}),"",VLOOKUP(L1,B2:J31,5,0))
But as far as I'm aware you cannot use >0 or #N/A

 

[sbawnh]

THANK YOU! Love the use of a dictionary. Will need to figure out what else I can stuff into it.

Is this formula any better? (Adding a lot of reading complexity for one less Vlookup)

=IF(D2="","",IFERROR(IF(OR(VLOOKUP($D2,'Worksheet'!$A:$Q,17,FALSE)={0,""}),"",VLOOKUP($D2,'Worksheet'!$A:$Q,17,FALSE)),""))

Compare

=IF(D2="","",IF(ISNA(VLOOKUP($D2,'Worksheet'!$A:$T,17,FALSE)),"",IF(VLOOKUP($D2,'Worksheet'!$A:$Q,17,FALSE)=0,"",(VLOOKUP($D2,'Worksheet'!$A:$Q,17,FALSE)))))

 

[Fluff]

This one
=IF(D2="","",IFERROR(IF(OR(VLOOKUP($D2,'Worksheet'!$A:$Q,17,FALSE)={0,""}),"",VLOOKUP($D2,'Worksheet'!$A:$Q,17,FALSE)),""))
can be simplified to
=IF(D2="","",IFERROR(IF(VLOOKUP($D2,'Worksheet'!$A:$Q,17,FALSE)=0,"",VLOOKUP($D2,'Worksheet'!$A:$Q,17,FALSE)),""))
There's no need to check if it returns "" & then make it return the same.

 

[sbawnh]

Thanks!

True, that will be resolved with the IFERROR.

You da best.

 

[Fluff]

You're welcome & thanks for the feedback