Excel VLOOKUP Yes or No: How to Return a Match Result

Formulas & functions · Updated November 18, 2026

Yes. In Excel for Microsoft 365, you can make VLOOKUP return “Yes” or “No” by wrapping an exact-match lookup in a formula that handles whether a value is found. VLOOKUP by itself returns a matching value or an error; it does not create a yes/no answer automatically.

For a simple check, look up a value in a list and return “Yes” when it exists and “No” when it does not. If the lookup should also check a status in another column, use VLOOKUP to retrieve that status and then convert it into a yes/no result.

How VLOOKUP Handles a Match

VLOOKUP searches down the first column of a selected table and returns a value from another column in the same row. It is useful when a key, such as a product code or employee ID, appears in the leftmost column of the lookup range. The function does not directly report whether it found a match in plain language: if it finds the key, it returns the requested column’s value; if it cannot find the key, it usually returns the #N/A error.

To turn that behavior into “Yes” or “No,” combine the lookup with a logical function. An exact-match lookup is important for most yes/no checks. In VLOOKUP, the fourth argument FALSE requests an exact match. If you omit that argument, VLOOKUP uses approximate matching, which can produce an unexpected result unless the first column is sorted appropriately for that type of lookup.

Exact matching is not case-sensitive: for example, Excel generally treats “abc” and “ABC” as the same text for this lookup. VLOOKUP can also interpret the asterisk (*) and question mark (?) as wildcard characters in an exact-match text search, which matters if those characters are part of the data rather than intended as wildcards.

Return Yes When a Value Appears in a List

For a straightforward membership check, use ISNA to detect the #N/A result that means VLOOKUP did not find the key. Assume IDs are in A2:A6 and the ID to check is in E2. The formula below returns “Yes” if E2 matches one of the IDs and “No” if it does not:

=IF(ISNA(VLOOKUP(E2,$A$2:$A$6,1,FALSE)),"No","Yes")

The lookup range contains only the ID column, so the column index is 1. If E2 contains ID 104 and that value is in A2:A6, the result is “Yes.” If E2 contains 999 and that ID is absent, VLOOKUP returns #N/A, ISNA detects that result, and the formula returns “No.” The dollar signs keep the list range fixed if you copy the formula down to other rows.

The order of the last two IF results is intentional: IF tests whether the lookup produced #N/A. If it did, the formula returns “No”; otherwise, it returns “Yes.” If your list contains a blank cell, a blank lookup value may match that blank. Decide how blanks should behave before relying on the result, particularly if users can leave the input cell empty.

Check a Returned Status, Not Just Whether a Key Exists

Sometimes finding a code is not enough. You may want “Yes” only when the matching record has a particular status, such as “Approved.” Assume product codes are in A2:A100, their statuses are in B2:B100, and the code to check is in E2. This formula returns “Yes” for an Approved record and “No” for a different status or a missing code:

=IFERROR(IF(VLOOKUP(E2,$A$2:$B$100,2,FALSE)="Approved","Yes","No"),"No")

Here, the table range starts with the code column and includes the status column. The column index 2 tells VLOOKUP to return the status. IF compares that status with “Approved”; IFERROR supplies “No” if the lookup cannot find the code. For example, if code P-17 has status Approved, the result is “Yes.” If P-17 has status Pending, the result is “No.” An absent code also produces “No.”

Be aware that IFERROR catches errors beyond a missing match. If a source value or a different part of the calculation has an error, this formula may label that case “No” too. That may be acceptable for a simple report, but it can conceal a data problem. If you need to distinguish “not found” from other errors, use an explicit ISNA check and decide how to display other errors separately.

What Each Part of the Formula Means

In the list-check formula, the outer IF chooses the final text, while ISNA evaluates the result of VLOOKUP. VLOOKUP receives four pieces of information: the value to search for, the lookup table, the number of the column to return, and whether to use an exact or approximate match. In a yes/no existence test, the lookup table can be just the list column and the return-column number is 1.

  • Lookup value: the value being checked, such as E2.
  • Table array: the cells containing the lookup list or the key and related data.
  • Column index: the position of the return column within the selected table, counting from its left edge.
  • Match setting: FALSE for an exact match in the examples here.
  • IF result text: the words Excel should display for each outcome, such as “Yes” and “No.”

That column index is a common source of mistakes. If the table range is A2:C100, column A is index 1, B is index 2, and C is index 3. The index is relative to the selected range, not the worksheet’s column letters. VLOOKUP also cannot return a value from a column to the left of the key column; if the layout requires that, consider changing the lookup design or using another function.

A Simpler Alternative for an Existence Check

If you only need to know whether a value appears in a list, COUNTIF is often more direct than VLOOKUP. Assume the list is in A2:A100 and the value to check is in E2. This formula returns “Yes” when COUNTIF finds one or more matching cells and “No” when it finds none:

=IF(COUNTIF($A$2:$A$100,E2)>0,"Yes","No")

For example, if E2 is 104 and 104 appears twice in the list, COUNTIF returns 2 and the formula displays “Yes.” A count greater than zero is enough; the formula does not need to know which row contains the match. If E2 is not in the list, the count is 0 and the result is “No.” This approach avoids choosing a return column and handling VLOOKUP’s #N/A result.

COUNTIF criteria can treat * and ? as wildcards, and its text matching is not case-sensitive. That behavior can affect unusual codes containing those characters. Also, COUNTIF is meant to count matches, not retrieve details from the matching row. Use VLOOKUP when you need a value associated with the key, such as a status, department, or price.

Common Problems and How to Fix Them

A “No” result when you expect “Yes” often points to a mismatch between the lookup value and the stored list, rather than a problem with the IF function. Check whether one value is stored as text and the other as a number. A number 104 and text “104” can behave differently in lookup comparisons. Also inspect leading or trailing spaces, inconsistent punctuation, and imported data that contains invisible characters.

  • Wrong column index: Count columns from the left edge of the table array. A range beginning in B makes column B index 1.
  • Approximate matching: Include FALSE when you need an exact match. Approximate matching is not the right default for a simple list-presence check.
  • Lookup column is not first: VLOOKUP searches only the first column of its table array. Reorder the range or choose a function that can search in the needed direction.
  • Unexpected “Yes” with blanks: Check whether the lookup input is empty and whether the list contains blank cells. Add a separate blank check if an empty input should not count as a match.
  • Formula displays as text: Confirm the cell is not formatted as Text, then re-enter the formula. A leading apostrophe can also cause Excel to display rather than calculate it.

For troubleshooting, test the lookup on its own in an unused cell and inspect what it returns before adding IF. That helps separate a lookup-range problem from a yes/no logic problem. Also verify that the formula’s range covers all relevant rows; a range ending at row 100 will not include a new record entered on row 101.

Choosing Between VLOOKUP, COUNTIF, and Newer Functions

Choose the formula based on what “Yes” is supposed to mean. If it means “this key appears anywhere in the list,” COUNTIF is clear and compact. If it means “the matched record meets this condition,” VLOOKUP can return the relevant field for a comparison. For example, checking an approval status requires retrieving that status, not merely confirming that the code exists.

XLOOKUP is another option in Excel 2021 and Microsoft 365. Unlike VLOOKUP, it can return data from a column to either side of the lookup column, and it has an argument for a value to use when no match is found. It is useful when you need a returned field, but for a basic membership question, COUNTIF may still be easier to read. Older Excel versions may not include XLOOKUP, so VLOOKUP remains useful for compatibility.

Whichever approach you choose, make the meaning of “No” explicit. Does it mean the key is absent, the status is not approved, the value is blank, or the source data is invalid? Those are different conditions. A formula that collapses them all into “No” may be convenient for a summary, but a separate error or review label can be safer when someone needs to investigate exceptions.

Frequently Asked Questions

Can VLOOKUP return “Yes” if a value is found and “No” if it is missing?

Yes. Wrap an exact-match VLOOKUP in IF and use ISNA or IFERROR to handle the not-found result. For a simple existence check, COUNTIF can be a more direct alternative.

Why does VLOOKUP return #N/A instead of “No”?

#N/A means VLOOKUP did not find a matching value. VLOOKUP does not automatically convert that error to text. Wrap it in a formula that handles the error and supplies “No” as the chosen result.

Should the last VLOOKUP argument be TRUE or FALSE?

For a yes/no check that requires the value to match exactly, use FALSE. TRUE, or leaving the argument out, requests approximate matching and can return an unintended result for an unsorted list.

Can VLOOKUP check whether a status equals “Approved”?

Yes. Look up the key, return the status column, and compare that returned value with “Approved” inside IF. Decide separately how missing keys and other errors should be displayed.

Is COUNTIF better than VLOOKUP for a yes/no answer?

For a simple “does this value appear in this range?” check, COUNTIF is often simpler. VLOOKUP is useful when the yes/no decision depends on information in the matching row, such as its status or category.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.