• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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
0
johnsonsite
Asked:
johnsonsite
  • 2
1 Solution
 
ltswebCommented:
You should use: =VLOOKUP(A1|$B$1:$C$20|2|FALSE)

It populated the answer "T" instead of "N/A"

The reason is False will do an exact match which sounds counter intuitive, but it works!

Hope that helps!
0
 
johnsonsiteAuthor Commented:
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!
0
 
barry houdiniCommented:
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
0
 
johnsonsiteAuthor Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now