I. Issue
You get a message that the result cannot be found when using the VLOOKUP function.
II. Causes and solutions
Cause 1: The value you're looking for is not in the lookup range.
You can try the following to troubleshoot:
Method 1: Re-select the lookup range to ensure that it contains the value you're looking for.
Method 2: Set the fourth argument of the VLOOKUP function to approximate match to look for the best match instead.
Cause 2: The format of the value being looked up is not consistent with the value in the search range
In Sheets, you can change a value's number format, but that won't necessarily change how the value appears in the cell. For example, a value's number format can be number or text.
- Number: By default, they are right-aligned in the cell and can be used for formula calculations. There are a few number format options that specify how numbers are rounded or how many decimal places are there.
- Text: By default, they are left-aligned in the cell and cannot be used for formula calculations. A green triangle icon will appear in the upper-left corner of the cell. For more details, see What's a green triangle in the upper-left corner of a cell in Sheets?
250px|700px|reset
You can try the following to troubleshoot:
Method 1: To unify all the data to text format, select the cells containing the data, and press Ctrl + Shift + S (Windows)/⌘ + Shift + S (Mac) or double-click the cell to convert the data to text format.
Method 2: To unify all the data to number format, select the cells with a green mark, click the Exclamation mark icon, then click Convert to number, or double-click the cell.
III. FAQs