VLOOKUP range problem with Numbers 3.5.3

Question

AussieBBM Author

Level 1

6 points

VLOOKUP range problem with Numbers 3.5.3

Hi All,

I have a most peculiar problem, which I have only noticed since upgrading (?) to Numbers v.3.5.3.

I have a formula which is supposed to use VLOOKUP to return a value from a range in another table.

The problem is that I get an error: "VLOOKUP couldn’t find the requested value." and on editing the formula

I find that the second argument, which is supposed to be a collection containing the searched for and returned values,

indicates the following error: The range income::A2:E7 can't be used as a single value.

Can anyone shed some light on this please?

I'm running Numbers v3.5.3 on OS X 10.10.3 on an IMac 11,3.

iMac, OS X Yosemite (10.10.3), i7 Quad-Core model 11,3

Posted on Apr 24, 2015 3:46 PM

Reply

Answer 1

Apr 24, 2015 4:03 PM in response to AussieBBM

Please post a screenshot (omit personal or sensitive information)

Reply

Answer 2

Yellowbox

Level 7

20,383 points

Apr 24, 2015 7:31 PM in response to AussieBBM

Hi AussieBBM,

Please post your formula.

Regards,

Ian.

Reply

Answer 3

AussieBBM Author

Level 1

6 points

Apr 24, 2015 9:55 PM in response to AussieBBM

Thanks to all who replied. I have discovered a workaround, which is simply to have the column which contains the searched values as the first column in the range. If it is anywhere else then I get the error described. A little re-ordering of columns and the error goes away and everything works fine.

This is the formula which was giving the problem:

=IF(VLOOKUP(B3,Income::A2:E9,4,TRUE) ="Pension",VLOOKUP(B3,Income::A2:E9,5,TRUE),0) +IFERROR(VLOOKUP(A1,Additional:Table,3,FALSE),0)

B3 contains the period end date and initially I had that in column C of the Income sheet. I moved the date into column A and the VLOOKUP worked! Magic!

I tried to upload a screen shot, but couldn't do it for some reason (It was a .png file of less than 100 KiB).

Reply

Answer 4

Jeff Shenk

Level 4

2,993 points

Apr 25, 2015 9:29 PM in response to AussieBBM

According to the User Guide,

Usage Notes

VLOOKUP compares a search value to the values in the leftmost column of a specified range.

So there really isn't any magic involved; that's just how it is supposed to work.

Reply

Answer 5

t quinn

Level 5

7,043 points

Apr 25, 2015 8:13 PM in response to AussieBBM

Hi Aussie,

If you ever need that search column to be to the right of your return column you can use the famous INDEX() and MATCH() combo.

=INDEX(A,MATCH(C1,B,0),1,area-index)

this will look for the value in C1 in column B and return the value in column A.

quinn

Reply

Answer 6

AussieBBM Author

Level 1

6 points

Apr 25, 2015 9:29 PM in response to Jeff Shenk

Is my face red!?!

I misread the examples and the text of the help.

Sorry to waste people's time!

Reply

Answer 7

Jeff Shenk

Level 4

2,993 points

Apr 26, 2015 4:28 AM in response to AussieBBM

Certainly nothing to be embarrassed about. I think Apple's documentation sometimes gets caught between trying to explain a fairly complex syntax and doing it in a concise manner, and sometimes they miss in the direction of being concise. It doesn't help that the syntax for the various lookup functions isn't particularly consistent, but that is probably a legacy from other spreadsheet programs.

You aren't wasting anybody's time; nobody has to be here; if our time is being wasted here, we're doing it ourselves because we enjoy it. And, more importantly, your question and the answer you found may be helpful to someone else who is having a similar problem.

Reply

Answer 8

Yellowbox

Level 7

20,383 points

Apr 26, 2015 12:27 PM in response to AussieBBM

Hi Aussie,

AussieBBM wrote:

Is my face red!?!

I misread the examples and the text of the help.

Sorry to waste people's time!

You are not wasting anybody's time. To get help on a function, type = in any table cell to bring up the Function Browser. Type the first few letters of the function in the Search box for an explanation that is much better than the online help:

Or in the table cell, type =vl

Then type open bracket (open parenthesis) to confirm and see the syntax for that function:

Click on a "lozenge" then click on a cell or drag over a range.

Regards,

Ian.

Reply