Numbers vlookup return value is one row off

Never seen that. But if there is no exact match to what you are searching for and if the function is not set to "exact match", it might return a value from whatever row it thinks is a "close match". Is this possible in your case? Maybe your search term and the row you think is a match are not exactly the same because of an unseen space or a different character or an extra decimal place that is not being displayed or something.

Can you post a screen shot showing the formula, the columns containing the 'found' entry and the column containing the returned entry.

Its difficult to diagnose an issue without seeing the details.

Regards,

Barry

To take a screenshot of part of the screen:

• Place the mouse pointer at the top left corner of the area to be captured.

• Press shift-command-4

(The pointer will change to a crosshair)

• Press and hold the mouse button and drag the crosshair to the bottom right corner of the area to be captured.

• When the selection rectangle holds the area you want to capture, release the mouse button.

(You'll hear a 'shutter click,' and the image will be saved as a file on your desktop, with the name Screen Shot, followed by the date and time the shot was taken.)

Your screenshot of a table should be taken while the table or one of the cells in the table is selected, making the table active, and ensuring that the row and column reference tabs are visible. Please include these tabs in any screenshot images.

To insert an image in your post:

• Place the insertion bar at the point you want the image inserted.

(I usually press a return after the line of text I want above the image.)

• Click the 'two mountains' button, second from Right in the set below the message composition window.

(This will take you to a Finder window showing the files on your Desktop. If they are sorted by Date, descending from most recent, your screen shot should be at or very near the top of the list.)

• Double-click the Screen Shot's icon.

(The window will close, you;ll be taken back to your message, and after a short pause, the image will appear in your message.)

• Press return to start a new line below the image, and carry on.

I do not know the logic behind what it thinks is a close match with strings. With numbers it is the "largest left-column value that is less than or equal to the search". Perhaps because "AAPL " is five characters, it is not "less than or equal to "AAPL". I note that Excel gives me the same answer. Compatibility with Excel is an important factor.

If you include the "exact match" parameter in the VLOOKUP function you will not have this problem. It will, instead, give you an error triangle when it does not find an exact match. To me that is better than a wrong answer.

VLOOKUP($A3,Analysts::A:C,3,0)

If you use the new XLOOKUP function instead, you can specify what do to if there is no match, such as returning the string "No match". You can do the same with IFERROR(VLOOKUP(), "No Match")

Just a note regarding the screen shots: Using View > Show Invisibles before taking the screen shot will show any invisible characters in the cells, and could help determine the issue.

REgards,

Barry