Excel函数合并求助：将VLOOKUP嵌入IF函数以避免错误提示

Got it, let's work through merging these two formulas exactly how you need it. The goal is to only run the VLOOKUP when your condition is met, and return "NO" both when the condition fails and when the VLOOKUP would throw an error (like if the lookup value is below your threshold or not found).

Final Formula #

=IF(C5>=$J$4, IFERROR(VLOOKUP(C6-500,$H$5:$I$8,2,FALSE), "NO"), "NO")

Breakdown of How It Works #

Let's break this down piece by piece so you understand every part:

• Outer IF Check: First, we check if C5>=$J$4. If this is false, we immediately return "NO"—no need to run the VLOOKUP at all.
• VLOOKUP with Error Handling: If the condition passes, we run your VLOOKUP, but wrap it in IFERROR. This catches any errors the VLOOKUP might throw (like #N/A when the lookup value isn't in $H$5:$H$8, or invalid references). If an error occurs, it returns "NO" instead of the error message.
• Exact Match Note: I added FALSE as the fourth argument to VLOOKUP. Your original formula omitted this, which defaults to approximate matching (only works if your H column is sorted). Using FALSE ensures we only get an exact match for your lookup value—adjust this back if you actually need approximate matching, but exact is safer for most cases like this.

Example Scenarios #

• If C5 >= $J$4 and C6-500 exists in $H$5:$H$8: Returns the corresponding value from column I.
• If C5 >= $J$4 but C6-500 doesn't exist in the lookup range: Returns "NO" instead of an error.
• If C5 < $J$4: Returns "NO" right away.

内容的提问来源于stack exchange，提问作者Dkp