Jul 15 2024 09:30 AM
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!
Jul 15 2024 09:32 AM
Jul 15 2024 10:00 AM
Jul 15 2024 10:03 AM
Jul 15 2024 10:32 AM - edited Jul 15 2024 10:39 AM
Solution
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), ""))
Jul 15 2024 11:06 AM
=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.
Jul 15 2024 12:36 PM
Jul 15 2024 10:32 AM - edited Jul 15 2024 10:39 AM
Solution
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), ""))