Trouble with vlookup range items - getting error message

Question

Level 1

8 points

Trouble with vlookup range items - getting error message

I am trying to pull specific info from Table 1 on the Items sheet into a recipe card table on a second sheet. I have both typed and copied/pasted the text from Table 1 onto A3 of the recipe card sheet. (ignore comma at end of formula - it's been removed). In the formula error, the orange range gives error message "The range Items:Table 1::A2:J39 can't be used as a single value." I have used both the function wizard and typed without wizard. Can anyone help?

Numbers-OTHER, OS X Yosemite (10.10.5)

Posted on Aug 16, 2016 8:15 AM

Reply

Answer 1

SGIII

Level 8

37,562 points

Aug 16, 2016 8:50 AM in response to demwalk

The syntax for VLOOKUP, per help entry here, is

VLOOKUP (search-for, columns-range, return-column, close-match)

The function expects a column number for the third parameter return-column whereas you have the range ITEM::Table1::CAL. Try changing that range to the number 3. You will need to enter the appropriate numbers for the other columns as you enter the formula across. (There are fancier ways to automate the column number using MATCH but if you are not frequently setting up new tables like this then it is probably easiest to just enter the relevant column numbers.)

SG

Reply

Answer 2

demwalk Author

Level 1

8 points

Aug 16, 2016 9:06 AM in response to SGIII

There is no error message for that part of the function, but I tried anyway, with no change. The error is still the 2nd parameter, "Items::Table 1::A2:J40" - I don't understand the error message "The range Items:Table 1::A2:J39 can't be used as a single value." But thanks for the response!

Reply

Answer 3

t quinn

Level 5

7,063 points

Aug 16, 2016 9:12 AM in response to demwalk

Hi demwalk,

I want to put in a plug for the INDEX/MATCH combo in place of the LOOKUP functions. I use it in place of all of them- it is very versatile.

One of the things I appreciate about it it that it is very clear where everything refers to.

In the above:

We are matching for the value in A2 in Table 1::A

we are retrieving values from Table 1::B in the row we found A2.

quinn

Reply

Answer 4

SGIII

Level 8

37,562 points

Aug 16, 2016 9:22 AM in response to demwalk

It can help to get a couple of the examples in Help working, then apply the concept to your specific table setup. For the second parameter have you tried just selecting columns A through J rather than typing in a range?

Also, in your region you don't use ; as a decimal separator, do you? If you do, then substitute ; for the , in the formula.

SG

Reply

Answer 5

demwalk Author

Level 1

8 points

Aug 16, 2016 9:29 AM in response to t quinn

I love the formula, Numbers doesn't. Now have "This formula contains a syntax error." When I select each component of formula, none show where the problem resides. I have copied pasted cell reference from table to cell, made sure all text and cell formatting exactly matches (other than cell color)... oye, I've never had this trouble in Excel and I do all kinds of formulas there. Thank you!

Reply

Answer 6

demwalk Author

Level 1

8 points

Aug 16, 2016 9:33 AM in response to SGIII

I cannot get the Help examples to work either. I have tried selecting just columns, tried typing in the range, used the wizard, tried a new formula as recommended by quinn.

Also, only used , as separator.

Thank you, though!

I think there must be a program glitch in my system?

Reply

Answer 7

SGIII

Level 8

37,562 points

Aug 16, 2016 9:42 AM in response to demwalk

You may be overthinking this. I suspect you still haven't adapted to the Numbers formula editor. You wouldn't want to "copy pasted cell reference from table to cell". You just select the relevant parts of the table and Numbers does the work of entering the correct range references into the formula.

If you want to go back to basic Excel-style formula entry just choose "Show Formula as Text" in the dropdown.

But in general I find it easier to point and click as I assemble a formula. Numbers knows how to construct the range reference.

If you're getting syntax error messages when you are sure you should not be, then it could be, as suggested above, that you should be using ; instead of , in your region.

SG

Reply

Answer 8

t quinn

Level 5

7,063 points

Aug 16, 2016 9:41 AM in response to demwalk

Hi demwalk,

I want to suggest you build your formulas by clicking rather than typing. The resulting formula should comply with Numbers needs.

quinn

edit: what SG said.

Reply

Answer 9

demwalk Author

Level 1

8 points

Aug 16, 2016 10:46 AM in response to SGIII

Lol, I have often been accused of overthinking! Many thanks for the help from both of you!!

However, I have used all kinds of other formulas in Numbers as well and never had this issue. Not new to Numbers - just this particular error code.

I did the "Show Formula as Text" and this is the copy/paste result: Items::Table 1::CAL,MATCH(A3,DESCRIPTION,0). As you can see, only commas. (Numbers auto-changes the column references to the column descriptions and I haven't found a way to turn off that feature.)

Just to restate, I started from the beginning with the Wizard doing the point and click.

Using the Wizard, I selected just the columns, received the dratted error message.
Selected the formula, clicked on each component to see where error was indicated. It's only on the second component where search range is selected (Items::Table 1::A2:K39).
Then, using the Wizard, I selected the range of cells Items::Table 1::A2:K39, no change.
Next, abandoning the Wizard, I typed the entire formula, no change.
Next, I copied the text and style from the applicable cell in Table 1 and pasted into the reference cell of other table to make sure there were no text, hidden character or format discrepancies. No change.
I always have hidden characters turned on to make sure there is nothing hiding out of plain sight - so, there is nothing hidden causing the problem.
Next, I added absolutes to the range Items::Table 1::$A$2:$K$39, no change.
Then I finally asked the question on Apple Community.
Next, I tried the INDEX/MATCH combo in place of the LOOKUP function suggested by Quinn. The error code was a syntax issue, but no part of the formula, when clicked on, revealed which component had the issue.
Then tried your suggestion of copying the sample Tables from help and getting vlookup to work. No change.
Regarding comma use in formula, the Wizard places the comma. Then I typed the comma. Just for giggles and less hair pulling, tried typing the semicolon... which obviously doesn't work but last straw.

The error message "The range Items:Table 1::A2:J39 can't be used as a single value" - what does this mean? I have never seen this error code. There is nothing online regarding this specific sentence. A syntax error would be spelling, hidden characters, commas, dashes, extra spaces, etc. What does "can't be used as a single value" mean?"

For some reason, no formula will work when I select the range "Items::Table 1::A2:K39" as seen below. I keep getting the same message about "single value." (The selection range is only to show you that I am not skipping rows or columns.) The error is within Table 1. Column A is formatted for text. Columns B:E are formatted for numbers with one decimal. Column F is formatted for numbers, no decimal. Columns G:I are formatted for currency, accounting style. The remaining columns are formatted as text.

Do you think Numbers cannot recognize the different formats in columns?

Reply

Answer 10

SGIII

Level 8

37,562 points

Aug 16, 2016 12:19 PM in response to demwalk

No "accusations" of anything. We're just trying to help here. 🙂

From your descriptions it's hard to tell what may be going wrong on your end. (BTW, I don't think there is such a thing as a 'Wizard' in Numbers.). Have you tried quitting Numbers and restarting your Mac, then relaunching Numbers while holding down the shift key, and starting on a whole new document?

SG

Reply

Answer 11

Barry

Level 9

60,029 points

Aug 16, 2016 12:39 PM in response to demwalk

Hi demwalk,

" (Numbers auto-changes the column references to the column descriptions and I haven't found a way to turn off that feature.)"

Numbers > Preferences > General

Uncheck the indicated box.

Your VLOOKUP formula:

The error message "The range Items:Table 1::A2:J39 can't be used as a single value." does not make sense unless you have edited the formula since receiving that error message. With the formula as shown, ignoring the tiny mark left of the insertion point, the error message should be: "The range Items:Table 1::CAL can't be used as a single value."

Here's the syntax for VLOOKUP:

VLOOKUP(search-for, columns-range, return-column, close-match)

search-for, --a 'single value' or reference to a cell (A3) containing the value to search for.

columns-range, --the full range of the lookup table (Table 1::A:J) (preserve both columns if filling the formula right or left)

return-column, --a 'single value' telling the column from which the value is to be returned.
The value must be a number. column 1 is the leftmost column of the lookup table (A).

For "CAL", this number is 3 (if using the table in your original post, where CAL is column C),

or 2 (if using the table in your most recent post, where CAL is column B).

close-match --An optional value. If omitted, or set to close-match, the formula will accept the

'largest value less that or equal to" the search-for value. If set to exact-match, the formula will

accept only an exact match, and will return an error if that cannot be found.

For use in the location shown in your original post, and with the lookup table shown in your original post, your VLOOKUP formula should look like the one shown below the Main table below:

VLOOKUP($A3,Table 1::A:K,3,FALSE)

(FALSE will display as "exact-match" in the formula editor.)

Regards,

Barry

Reply