CODEWITHSUNDEEP
excel-formulaexcel-365
Dan
Dan

Reputation: 11

VLOOKUP results in #N/A with Exact Match

Using Microsoft 365 Excel.

In cell K2, I have:

=VLOOKUP(F2,METERS,2,FALSE)

In cell L2:

=VLOOKUP(G2,KNOTS,2,FALSE)

The result in L2 is what I expect.

No matter what I do to the equation or anywhere in the workbook, I keep getting #N/A in K2.

When I replace the named range in K2, METERS for KNOTS, the function evaluates properly.

Copying the named range works properly, so there's nothing wrong with the named range:

=METERS

returns the complete named range without errors.

Checking the value in F2 evaluates as TRUE, so the value is an exact match:

=(F2=MS!A210)

Oddly enough, if I change the function in K2:

=VLOOKUP(F2,METERS,2,TRUE)

then it sort of works, in that it gives me a value instead of #N/A, but doesn't give me exactly the result I need.

I tried moving the function from K2 to different cell and using a different cell for the lookup value; then no matter what I input, I get 0.

I know the syntax in K2 is correct. The named range is correct. Everything seems to be correct.

Yet no matter what, I get #N/A.

Upvotes: 1

Views: 936

Answers (1)

JB-007
JB-007

Reputation: 2525

Here/screenshot(s) refer:

Given the info you have provided, the only reasons I can think of why this could occur are:

a) Conflicting named ranges with common names/different scopes

b) Inconsistency between the two named ranges you're using

These aren't mutually exclusive (a different scope could lead to an inconsistency). I'll demonstrate the following in this solution:

  • No issues arise when using two different names that refer to the exact same range / sheet / scope
  • How easily issues can arise due to duality of scope (e.g. worksheet vs. workbook) for common names, and how either of these can be referenced anywhere in the workbook

Finally, I'll comment on the 'true/false' indicator in the vlookup function you mentioned, and propose an alternative / improved feature avail. with Office 365.

Context

Conflicts RE: named ranges can arise in several ways - e.g. when multiple sheets contain named ranges that have common names, when copying a function that comprises a named range from one sheet to another, etc. It's possible to produce a conflict from one workbook to another in this fashion too.

When copying such a function from one sheet to another you should see the something like the following UI appear:

Resolve conflict dialogue box

Sheet 1 - correct referencing

Comprises two named ranges (range_1, range_2 resp.) which both refer to the exact same range (with identical scopes). Vlookups operate perfectly fine and produce identical results:

Sheet 1 - consistent results

Besides their name, range_1 and range_2 are otherwise identical in every way per the following in Name Manager:

Name Manager - identical ranges/scope

Sheet 2 - conflict / error

Copy of Sheet 1, with range_1 amended to only include the 1st column - the same vlookup functions as Sheet 1 now produce an error for range_1:

Sheet 2

Note: copying the functions from Sheet 1 have now produced the following in Name Manager:

Various scopes - local and workbook However, I'm still able to reference range_1 from Sheet 1 per the following tooltip that appears when I start typing '=range_1' in any cell within Sheet 2:

Tooltip referencing various scopes for common named ranges

Thus its possible to retrieve what appears to be the 'correct' range for range_1 in any cell in the workbook by referencing the original scope (workbook), despite the vlookup in Sheet 2 producing an error.

It this is not the case (and you do not have 'duality' in scopes for the same named range) then the only other reason I can think of is inconsistency RE: formulation. If they are truly identical in the ranges they reference, then I would suggest adopting a single name (KNOTS, given this appears to function correctly) to simplify the workbook/functions and improve auditability.

Vlookup parameter (0/1)

RE: vookup(range, lookup, True/False) -

True (i.e. '1') parameter will return an 'approximate' match (which distinguishes this function from alternatives such as index/match). However, xlookup is advisable / preferred over vlookup in any case given it also has this feature and can reference to the left or right of the lookup column (vlookup works strictly to the right). Although it is more complex with additional parameters/arguments to consider. See here for details.

Upvotes: 1

Related Questions