asked on

Excel vlookup problem

In the attached spreadsheet, I cannot understand why it does what it does. How can D1 - D10 be #N/A, but then D11 - D20 yield that result? It all makes no sense, even in this cheesy-easy example. I made this simple test case because in my big complicated spreadsheet it wasn't working either
Book3.xls

ASKER CERTIFIED SOLUTION

ltsweb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnsonsite

ASKER

Yes, not intuitive. All the web searching I did (hours worth) never exposed that trick. It really should work fine the other way too, with TRUE (the default). Stupid Microsoft.

Anyway, thanks a lot!

barry houdini

Sometimes Excel help is the best place to look. My version says this about the final (4th) argument:

range_lookup Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

Important If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

Note that last part, TRUE will only work when the lookup array is sorted ascending - yours is sorted descending, hence #N/A results.

regards, barry

johnsonsite

ASKER

Well, that was a bad example I sent out then. In my real spreadsheet the values are ascending. Either way, adding the FALSE parameter cured the problem, although it doesn't make sense. If a match is an exact match, then it would ALSO satisfy being a partial match. The function just doesn't even work if the range_lookup is TRUE or omitted, it returns mostly garbage.

Thank you!