Forum Discussion
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), ""))
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.
