Vlookup with mulitiple outputs

[Caracarn]

I'm trying to use a Vlookup function, but I want it to have multiple possible outputs. All of the other help I've seen seems to just want to outputs, an error, and a correct. I'm trying for three different outputs. I've tried searching for help multiple different ways, but I can't seem to find something that is exactly what I'm looking for.

If the input is blank I want the output to be blank.
If the input is an item in my list I want it to output the ingredients for that item.
If the input is not an item in my list I want it to output "Not in List".


This is what I've tried so far:

=IFERROR(IF(VLOOKUP(B2,Recipes!A:B,2,false)="","",VLOOKUP(B2,Recipes!A:B,2,false)),"Not in List")

If I type a correct input it gives me a correct output, If I type an incorrect input it gives me the "Not in List" error, but If I leave my input cell blank it doesn't leave the output cell blank. It gives me the "Not in List" error again.

Any help would be much appreciated!

 

[BSALV]

your problem is, without the check if(vlook(..)="","" you receive a 0 as result.
If you hide that zero with conditional formatting or by a setting in your workbook for each worksheet, you don't need this extra if

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(B2="","",IFERROR(IF(VLOOKUP(B2,Recipes!A:B,2,FALSE)="","",VLOOKUP(B2,Recipes!A:B,2,FALSE)),"Not in List"))

 

[Caracarn]

your problem is, without the check if(vlook(..)="","" you receive a 0 as result.

I tried removing the IF(Vlookup(...)="","" which left me this

=IFERROR(VLOOKUP(B2,Recipes!A:B,2,false)),"Not in List")

This leaves me with an error message though.

 

[Caracarn]

Hi & welcome to MrExcel.
How about

=IF(B2="","",IFERROR(IF(VLOOKUP(B2,Recipes!A:B,2,FALSE)="","",VLOOKUP(B2,Recipes!A:B,2,FALSE)),"Not in List"))

Well I'll be. That works! Thanks for the help! Do I still need to have the additional

VLOOKUP(B2,Recipes!A:B,2,FALSE)="",""

? Since the B2="","" is outputting a blank if the input is a blank.

 

[Sufiyan97]

Try

=IFERROR(IF(B2="","",IF(VLOOKUP(B2,Recipes!A:B,2,0)="","",VLOOKUP(B2,Recipes!A:B,2,0))),"Not in List")

 

[Sufiyan97]

Do I still need to have the additional

VLOOKUP(B2,Recipes!A:B,2,FALSE)="",""

? Since the B2="","" is outputting a blank if the input is a blank.

Yes you still need that

 

[Caracarn]

your problem is, without the check if(vlook(..)="","" you receive a 0 as result.
If you hide that zero with conditional formatting or by a setting in your workbook for each worksheet, you don't need this extra if

I just realized that I read your first reply completely in correctly. I completely overlooked the 'check if' statement. I apologize. I just saw "without vlook=""" So i deleted it, I realize now what your first comment was telling me. Sorry for the trouble.

 

[Fluff]

? Since the B2="","" is outputting a blank if the input is a blank.

Yes you do still need that incase col B on Recipes is blank.
However if this only needs to work in xl2021, it can be simplified.