The VLOOKUP formula is a powerful tool in Excel that allows you to search for values in a table and return corresponding values from another column. However, when dealing with Yes/No values, it can be challenging to get the desired results. In this blog post, we will explore how to modify the VLOOKUP formula to return both Yes and No values when searching for a specific value in a table.

The Original Formula

———————-

The original formula provided by the user is as follows:

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

This formula is attempting to return the value in cell G1528 from the table starting at cell G5 and ending at cell Q1494, column 11. The issue with this formula is that it only returns a Yes value if the specified value is found in the table, and ignores any No values.

Modifying the Formula

———————-

To modify this formula to return both Yes and No values, we can use the following formula:

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

This modified formula first checks if the VLOOKUP function returns an error value. If it does, it returns the string “No”. If it doesn’t return an error value, it checks if the value returned from the VLOOKUP function is 0. If it is, it returns the string “No”. Finally, if the value returned from the VLOOKUP function is not 0, it returns the original value.

Testing the Formula

——————-

To test this modified formula, let’s plug in some sample values and see how it works.

Assume we have a table starting at cell G5 and ending at cell Q1494, with column 11 containing Yes/No values. We want to find the value in cell G1528 in this table and return both Yes and No values if they exist.

If we enter the following formula in cell Q1495:

=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)))

And fill it down into many cells, we should get the following results:

| G1528 | Q1495 |

| — | — |

| 123 | Yes |

| 456 | No |

| 789 | Yes |

| 012 | No |

As we can see, the formula returns both Yes and No values when searching for the value in cell G1528. This is because the VLOOKUP function returns a value of 0 for the No values, which triggers the second IF statement to return the string “No”.

Conclusion

———-

In this blog post, we explored how to modify the VLOOKUP formula to return both Yes and No values when searching for a specific value in a table. We discussed the original formula and how it can be modified to achieve the desired results. By using the modified formula provided above, you should be able to return both Yes and No values when searching for a value in a table.