SOLVED

# Modify formula to return false values too

Brass Contributor

# Modify formula to return false values too

Q5:Q1494 contain yes and no values and are custom formatted where a 1 returns "yes" and 0 returns "no"

The following formula is inserted into cell Q1495 and filled down into many cells

=IF(ISERROR(VLOOKUP(G1528,\$G\$5:\$Q\$1494,11,0)),"",IF(VLOOKUP(G1528,\$G\$5:\$Q\$1494,11,0)=0,"",VLOOKUP(G1528,\$G\$5:\$Q\$1494,11,0)))

The problem with this formula only 1 "yes" is returned, 0 "no" are ignored

What needs changed in the formula so that 0 "no" are returned also?

Thank you if you can help!

6 Replies

# Re: Modify formula to return false values too

=IF(ISERROR(VLOOKUP(G1528,\$G\$5:\$Q\$1494,11,0)),"",VLOOKUP(G1528,\$G\$5:\$Q\$1494,11,0))

# Re: Modify formula to return false values too

I should have mentioned cells where the formula is being used will not be custom formatted.

There is a problem with the modified formula any empty cells in Q5:Q1494 return 0

The formula just needs to return either yes or no and if there is neither return an empty cell

# Re: Modify formula to return false values too

My last sentence may not be crystal clear, I will re-phrase it....

The formula just needs to return either yes or no (as text) and if there is neither return an empty cell
best response confirmed by packie (Brass Contributor)
Solution

# Re: Modify formula to return false values too

I think you are saying that column Q contains the numeric values 1 and 0, which only appear to be "yes" and "no" due to cell formatting.  (But that does not matter, in the final analysis.)

And you want to the formula to return the value in column Q, but return the null string if the Q cell is empty, as well if the VLOOKUP fails.

The problem is:  as you used it, VLOOKUP returns zero if the Q cell is empty.

One solution for all versions of Excel (2010 and later):

=IFERROR(IF(VLOOKUP(G1528, \$G\$5:\$Q\$1494, 11, 0)) = "", "", VLOOKUP(G1528, \$G\$5:\$Q\$1494, 11, 0)), "")

Your mistake was testing VLOOKUP(...)=0 instead of VLOOKUP(...)="".  VLOOKUP interprets an empty cell differently, depending on context.

If your version of Excel supports the LET function, a better solution is:

=LET(x, VLOOKUP(G1528, \$G\$5:\$Q\$1494, 11, 0), IFERROR(IF(x = "", "", x), ""))

# Re: Modify formula to return false values too

=IFERROR(IF(VLOOKUP(G1528,\$G\$5:\$Q\$1494,11,0)=1,"yes",IF(VLOOKUP(G1528,\$G\$5:\$Q\$1494,11,0)=0,"no","")),"")

# Re: Modify formula to return false values too

Using the LET function in combination with custom formatting done the trick.

Thank you guys for the help.
1 best response

Accepted Solutions
best response confirmed by packie (Brass Contributor)
Solution

# Re: Modify formula to return false values too

I think you are saying that column Q contains the numeric values 1 and 0, which only appear to be "yes" and "no" due to cell formatting.  (But that does not matter, in the final analysis.)

And you want to the formula to return the value in column Q, but return the null string if the Q cell is empty, as well if the VLOOKUP fails.

The problem is:  as you used it, VLOOKUP returns zero if the Q cell is empty.

One solution for all versions of Excel (2010 and later):

=IFERROR(IF(VLOOKUP(G1528, \$G\$5:\$Q\$1494, 11, 0)) = "", "", VLOOKUP(G1528, \$G\$5:\$Q\$1494, 11, 0)), "")

Your mistake was testing VLOOKUP(...)=0 instead of VLOOKUP(...)="".  VLOOKUP interprets an empty cell differently, depending on context.

If your version of Excel supports the LET function, a better solution is:

=LET(x, VLOOKUP(G1528, \$G\$5:\$Q\$1494, 11, 0), IFERROR(IF(x = "", "", x), ""))