Why do some lookup results appear correct but match the wrong inventory record?

Treat the result as unverified until you can explain how the formula selected it. Start with three separate checks: the key value, the match setting, and the lookup range. These address different failure modes, so changing the formula before inspecting the data can hide the actual problem.

For example, suppose a sheet called Inventory has item codes in column A, descriptions in B, and locations in C. A lookup on a second sheet might be intended to find the code in A2 and return its location. Write down the expected key and expected record from the source sheet, then compare them with what the lookup returned. This is an illustrative setup; use your own inventory as the reference.

Also check the key itself for differences that may be hard to notice in the displayed cell, such as an unintended space or inconsistent formatting. Confirm the actual key values in your file rather than assuming that similar-looking codes represent the same item.

If you are using Google Sheets VLOOKUP and intend to find a specific item code, explicitly set its final argument to FALSE. Google Sheets uses exact matching when that argument is FALSE; if you omit it, the default is TRUE, which means approximate matching. With approximate matching, the search-key column needs ascending sort order, and a missing key can return the preceding value—an output that may look plausible while being wrong. [S1]

For the example above, the intended formula is =VLOOKUP(A2, Inventory!A:C, 3, FALSE). Here, A2 is the search key, Inventory!A:C is the range, 3 selects the third column within that range, and FALSE requests exact matching. [S1]

If your result changes after making the match setting explicit, investigate that difference before using the output. This check applies to VLOOKUP in Google Sheets; do not assume another spreadsheet program or lookup function has identical settings without checking its documentation.

Inspect the formula’s range from left to right. VLOOKUP searches the first column of the supplied range, and its column index counts from that range’s first column—not necessarily from column A in the whole sheet. It returns data from the column identified by that index. [S1]

Using Inventory!A:C with an index of 3 returns the third column of that range, C. But if the range is instead Inventory!B:D, the first searched column is B, and index 3 refers to D. A range shifted one column can therefore make a formula return a valid value from an unintended field. [S1]

Compare the formula with the table headings: does the first range column actually contain the key, and does the return index land under the intended heading? Check copied formulas too; a neighboring row may refer to a different range or use a different index. Do not trust a correct-looking description or location until you have tied it to the expected record.

Check whether the key appears more than once in the source data. An exact-match setting does not prove that a key uniquely identifies one inventory record. The Google Sheets guidance does not establish whether a particular inventory key is unique, so check that separately. [S1] If you find repeated keys, identify which field or combination of fields distinguishes the intended records; do not treat an arbitrary-looking result as confirmation.

Copy and fill in this audit note for a suspect result:

Lookup cell: [cell] Search key: [value] Expected record: [record or reference] Exact-match setting: [FALSE / other / unknown] Range first column: [heading] Return index and heading: [index; heading] Repeated key found: [yes / no / not checked] Next check: [action]

For instance, a hypothetical note could read: Lookup cell: E2; Search key: ZX-12; Expected record: item at location R-4; Exact-match setting: FALSE; Range first column: Item Code; Return index and heading: 3; Location; Repeated key found: not checked; Next check: inspect all source rows with code ZX-12. Replace these example values with information from your workbook. The note records what you checked; it does not by itself establish that the inventory data is correct.