Excel Vlookup return value from reference_row-1 instead of reference row

Solution 1:

The comment by Mark Fitzgerald is on the right track, but not quite there.

The correct part is that the fourth parameter is both not specified AND not specified as FALSE.

When not specified, it does default to TRUE. It is also TRUE when specified as TRUE. Either way, you run into the problem you see here, but there is something else going on here which causes the precise unhappy result you are getting.

Before addressing that, I should point out that in a use such as yours, you clearly need an exact match for correct results. So you must add the fourth parameter and make it FALSE so that it looks for an exact match. The underlying problem I'm about to address won't matter then, which is a benefit.

The real problem comes from how you tell Excel what range to use for the lookup: the second parameter. You are using C:V which tells Excel to use every single cell in those columns. There are a BUNCH of reason NOT to do that, but the one that matter here is tied to the value of TRUE for the fourth parameter.

When it is TRUE, Microsoft tells us VLOOKUP() (and some other functions if looking things up when not expecting an exact match) will search through the data until it finds a cell that is "greater than" the lookup value, then stop, and return the value RIGHT BEFORE that "greater than" value. I will mention that this is not really the truth, but it is close enough for your difficulty.

Due to what they claim, they suggest correct results will only occur when the lookup column (column C here) is sorted, lowest value to highest value ("A-Z"). And that will work, almost always, even in your case, probably (but like 99.99% probably). Unfortunately, a huge number of people simply cannot do that sort without messing up some other functionality in their data. For most, they simply want it sorted according to other conditions and cannot sort by the lookup column.

So, what is happening to you? The lookup is looking for "CA-whatever" in column C. But... it is starting with the column header in cell C1. That header is "Barcode"... and so far so (sort of) good. Then in cell C2 it is finding something "greater than" the lookup value (which begins with "CA"). Let's say if finds, in cell C2, something alphabetically after both it and the header. It immediately stops and "retreats" back to the header cell. That's row 1 in the range, and it reads to the right into column S and finds "Room" in that cell (S1). That is what it returns.

In truth, if C2 was not past the lookup value alphabetically, it would surely have found something that was long before reaching C380 and have stopped at that earlier point, retreated one row, and returned that row's column S value. Which would ALSO have been wrong, but it might not have been so obviously wrong that you noticed it. After all, it would give some normal looking Room. All might look good. And yet, there could be easily be 4 or 32, or even 200-300 mistaken values RIGHT NOW.

As I mentioned at the start, you clearly need exact matches anyway, so add the fourth parameter to the lookup. Use FALSE, not TRUE. FALSE will force it to look for an exact match.

Since the problem was posted over two years ago, it seems likely you've solved it one way or another. But maybe this will be of use to someone else, someone who might find it helpful even for explaining add results with other functions that supposedly work like MS says, but really work like I said, although my touching on it was just a small slice of the whole subject! But any function that does lookups can run into it. MATCH() and XMATCH(), even the new XLOOKUP() function. Admittedly, the newer functions only run into it when using non-exact matching, but since they do and people do use them that way... And there are other times this arises.