I don't really follow the logic of the YES/NO link = hyperlink in your narrative but see the HYPERLINK function in XL Help Files.

If you need further assistance perhaps worth clarifying your original post:

The word yes should show as a hyperlink which directs to the cell in sheet 2 named Carrot. The word NO (cell b2) should direct to carrot in worksheet 2 (cell a1) based on the fact that that word is also contained in sheet 1 cell A2.
A1 contains Tomato and this is linking to Carrot on Sheet2 because ... ?
Why is B1 Yes and B1 No ?
You say the cell is "named" carrot - by that do you mean the cell contains the word Carrot or that the cell is indeed a named range ?