2

I'm having some issues using named ranges in formulas in excel from Microsoft Office 365 Business. This is something that I used to do constantly a few years ago, but now I stumped as to what I'm doing wrong... I'm assuming some functionality has changed with a later version of excel, but would like to check.

I have a vlookup for example like VLOOKUP($D11, $A$2:$B$14, 2, 0) that I would like to change to use a named range in the table array, for example like VLOOKUP($D11, LookupTable, 2, 0).

The line VLOOKUP($D11, LookupTable, 2, 0) however returns an error "There's problem with this formula. Not trying to type a formula...".

I've checked that the formula without the named range is working fine + that the named range itself has been set on the correct columns and that the scope of range is set to the workbook.

The error message suggests to me that the range isn't being resolved and is just being treated as a character string? (I've tried a few things like to specify the sheet as well > Lookups!LookupTable but still same issue)

Example of the file here

Sam Gilbert
  • 1,642
  • 3
  • 21
  • 38
  • What happens you you tap F5, paste in *LookupTable* and hit OK? –  Apr 04 '17 at 13:40
  • that action highlights the range correctly – Sam Gilbert Apr 04 '17 at 13:42
  • Are you on a system that uses semi-colons instead of commas for the list separator? i.e. should the formula be `=VLOOKUP($D11; LookupTable; 2; 0)` ? –  Apr 04 '17 at 13:43
  • ah... the default separator has been set to pipe, actual formula that is erroring is `=VLOOKUP($D11| LookupTable| 2| 0)` – Sam Gilbert Apr 04 '17 at 13:44
  • @Jeeped I read that the OP used the default separator, `|` and that it is giving the error when using it. And in the question, knowing that most of us use `,`, changed it to match the norm. So the problem still persist. – Scott Craner Apr 04 '17 at 13:48
  • Sorry not following, perhaps haven't explained myself correctly. My default delimiter has been changed to pipe, but the formula `VLOOKUP($D11| $A$2:$B$14| 2| 0)` works as expected, so I would expect `VLOOKUP($D11| LookupTable| 2| 0)` to also work? Please correct me if i'm missing your point :) – Sam Gilbert Apr 04 '17 at 13:49
  • 3
    Well, I created a new VLOOKUP using commas and a *LookupTable* defined name then reset my system list separator to **|** and reexamined the formula. It was correctly adjusted to =VLOOKUP($D11| LookupTable| 2| FALSE)` so I typed it in again manually with **|** and there was no problem. Sorry, I cannot reproduce your error. My only suggestion would be to use `=UNICODE(A1)` with the list separator in A1 to make sure it is 124. Repeat for the | you are using in the formula. –  Apr 04 '17 at 14:05
  • btw, if you mistyped *LookupTable* it would be entered as a #NAME! error, not the one that refuses to accept the formula as you are receiving. –  Apr 04 '17 at 14:09
  • @Sam, why cant you attach your excel with the post, if this is still not resolved? – Karpak Apr 04 '17 at 15:55
  • @Karpak attached an example file... – Sam Gilbert Apr 04 '17 at 18:03

1 Answers1

0

When I modified your excel it works perfectly fine. Not sure what is the problem. Not getting what is the problem. You can try the following. you can define a new named cell LTable with absolute reference as follows.

Sheet1!$B$5:$C$8

Then enter the formula using range.

VLOOKUP($D11, $B$5:$C$8, 2, 0)

Just $B$5:$C$8 with LTable

VLOOKUP($D11, LookupTable, 2, 0)

Follow these steps with whatever delimiter that you use. Two issues I suspect. The format of current cell and the LookupTable name may not be allowed. If the above procedure works, then problem could be any one of these in your system.

Karpak
  • 1,927
  • 1
  • 15
  • 16