Forum Discussion
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!
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), ""))
- 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- 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
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), ""))
- Using the LET function in combination with custom formatting done the trick.
So pleased with the result.
Thank you guys for the help.
