Issue referencing a named range in a worksheet formula

1
votes

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

1
excelexcel-formulanamed-ranges
What happens you you tap F5, paste in LookupTable and hit OK? – user4039065
that action highlights the range correctly – Sam Gilbert
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) ? – user4039065
ah... the default separator has been set to pipe, actual formula that is erroring is =VLOOKUP($D11| LookupTable| 2| 0) – Sam Gilbert
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. – user4039065

1 Answers

0
votes

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.