1

I have the following VLOOKUP on a worksheet:

=VLOOKUP(D5,FilePurposes,2)&" in respect of "&C5

Where the named range FilePurposes refers to a block of two columns and three rows that look like this:

   A       B
   ------------------------------------
4  x       Creates output for x
5  y       Calculates something for y
6  z       Refreshes z

When D5 = x I get "Calculate something for y", however. The required output is "Creates output for x". The VLOOKUPS on y and z are working as required.

When I change the VLOOKUP to

=VLOOKUP(D5,Sheet1!A4:B7,2)&" in respect of "&C5

(ie extending the range) then the VLOOKUP works as intended. However I want to use a named range whose defined range can be changed by the user to include valid lookup values as the workbook grows.

When I go to name manager, the defined range for FilePurposes is =Sheet1!$A$4:$B$6, so nothing immediately appears to be wrong.

Is there something about VLOOKUPS and named ranges that I'm missing here which causes this problem?

I am using Excel 2010 on a Windows Vista PC.

Improve this question
5
  • the named range you say is $A$4:$B$6 but $A$4:$B$7 in the formula example. What is in D5?
    – datatoo
    Jan 16, 2015 at 20:30
  • D5 = x. I extended the range to check whether VLOOKUP without the named range works, and it does. When I limit the range to row 6, Excel converts the specified range to its name, and then the VLOOKUP gives the incorrect result. This is quite puzzling!
    – Aaa
    Jan 16, 2015 at 23:55
  • 3
    Are you actually using "x", "y" and "z" or have you just simplified that for the question? If you use VLOOKUP with no 4th argument then the lookup range needs to sorted ascending.......and you may get a "closest match", e.g. if D5 = "yyy" you'll get the same result as if D5 = "y" - if you only want exact matches then use VLOOKUP with 4th argument of FALSE
    – barry houdini
    Jan 18, 2015 at 15:00
  • 1
    Thanks Barry! That does it - I forgot to specify the 4th argument. You're welcome to post your comment as an answer so I can give you recognition - I am truly grateful for your pointing out my silly mistake!
    – Aaa
    Jan 18, 2015 at 16:37
  • Great observation by Barry
    – datatoo
    Jan 18, 2015 at 20:27

1 Answer 1

Reset to default
0

Suspect the named range may be spelled differently

What you describe should work fine enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .