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), ""))
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
=IFERROR(IF(VLOOKUP(G1528,$G$5:$Q$1494,11,0)=1,"yes",IF(VLOOKUP(G1528,$G$5:$Q$1494,11,0)=0,"no","")),"")
Hope this will help you.