SOLVED

# Combine IF(Vlookup and IFERROR(Vlookup

Copper Contributor

# Combine IF(Vlookup and IFERROR(Vlookup

Is it possible to combine the two vlookup formulas listed below?  I have tried so many different ways with no success. I am using Microsoft Excel 2016.

Some of the data will have costs more or less than \$0 and some will have a cost equal to \$0.  Unfortunately some of the data being looked up from one sheet will not be listed in the lookup sheet. So I need the iferror to have a result of "No Data".  But if the Lookup Value (column A) does exist and is equal to 0 then the result needs to state "Cancelled".  For all other costs the result will be "Valid".  There will be no blank data.  Thank you for your help.

=IFERROR(VLOOKUP(\$A2,Sheet2!\$A:\$C,2,0),"No Data")

and

=IF(VLOOKUP(\$A2,Sheet2!\$A:\$C,2,0)=0,"Cancelled","Valid")

5 Replies
best response confirmed by 2Dogs6 (Copper Contributor)
Solution

# Re: Combine IF(Vlookup and IFERROR(Vlookup

=IFERROR(IF(VLOOKUP(\$A2,Sheet2!\$A:\$C,2,0)=0,"Cancelled","Valid"),"No Data")

Does this return the intended results?

# Re: Combine IF(Vlookup and IFERROR(Vlookup

Perhaps

``=IFNA( IF(VLOOKUP(\$A1,Sheet2!\$A:\$C,2,0)=0,"Cancelled","Valid"), "No Data" )``

# Re: Combine IF(Vlookup and IFERROR(Vlookup

I knew it was an easy answer just couldn't get anything to show up in a google search. This worked perfectly. Thank you.

# Re: Combine IF(Vlookup and IFERROR(Vlookup

This one worked perfectly as well. Thank you.

# Re: Combine IF(Vlookup and IFERROR(Vlookup

@2Dogs6 , you are welcome

1 best response

Accepted Solutions
best response confirmed by 2Dogs6 (Copper Contributor)
Solution

# Re: Combine IF(Vlookup and IFERROR(Vlookup

=IFERROR(IF(VLOOKUP(\$A2,Sheet2!\$A:\$C,2,0)=0,"Cancelled","Valid"),"No Data")

Does this return the intended results?