Reply to thread

Message: [QUOTE="joeu2004, post: 7405462"] Yes. You need to clean that up. All of the lookup values (1st VLOOKUP parameter) and all of the leftmost column of the lookup table (2nd VLOOKUP parameter) must be the same type, be it text or numeric. The choice is yours; but you must be consistent. Correct: many of the numeric VLOOKUP results are incorrect, not just those that are #N/A errors. Most of the values in column F are text, all except the zeros. And column A has a mix of numeric values (A5:A12) and text values (A13:A25). Presumably that is a result of a mix of types in the "Raw Data" worksheet. Moreover, you are using a "best match" lookup. That is, since you are missing the 4th VLOOKUP parameter, it defaults to TRUE. That means that VLOOKUP will use a binary search to try to find the largest value in column A less than or equal to the lookup value (1st VLOOKUP parameter). Ergo, the values in column A must be in ascending order according to Excel's default sort order. My guess is that you want an "exact match" lookup. In that case, the 4th VLOOKUP parameter should be FALSE. However, if you do that, it appears that some lookups will still fail (return #N/A) for lack of a match in column A. You might need to deal with that. But if you still want a "best match" lookup.... Normally, any text is considered larger than any numeric value according to Excel's default sort order. So when looking up the __text__ "990330501" (F5), I would expect VLOOKUP to return 5, the value in column C corresponding to 990378801, which is the last numeric value before the __text__ value "990372301" in column A. Note that "990330501" is less than "990372301". But apparently, VLOOKUP is a bit fickle when mixing numeric and text values. For example, VLOOKUP("a",{1,2,3},1) returns a #N/A error instead of 3 as I would expect. Be that as it may, the mix of numeric and text values, both for the lookup value and in the leftmost column of the lookup table, is the root cause of your problems, at least in part. Once you clean up that mess, perhaps some additional problems will surface. [/QUOTE]

Verification