Inconsistent VLOOKUP formula results with same formula

Question

Level 1

0 points

Inconsistent VLOOKUP formula results with same formula

I am having some issues with VLOOKUP (I'm a new iMac user, but an advanced Excel user). In a small table of value, when copying the same VLOOKUP formula down the column, some work and some come up with 'invalid reference" error. Just spent an hour and a half in a ONEtoONE at the Apple store and we couldn't figure it out for the life of us. It started with me wanting to add two VLOOKUP formulas together and that didn't work, then we realized that by copying the formula down some rows worked and some didnt. Very odd. Thoughts??

The formula is basic =VLOOKUP(A2, Breakfast Tab Name:Cost, Return Value of Cost, Exact Match)

Source Table:

The blank cells are the ones with the "invalid reference" error (red triangle with whote exlamation point". We tried a brand new workbook on another machine and the VLOOKUP wouldn't work at all. I have tried sorting all tables alphabetically, doesn't help. The weird thing is that when you copy the entre list of breakfast items into the rows beneath the ones in the sample above, they also all came back as errors. Yes, the names are exactly the same in both the source and destination tables as they have been copied/pasted over. Also in playing around and copying the formula a few rows down but pointing to one of the items above, it returned the name of the item, not the cost.

I hope I explained this clearly enough, I just can't understand why some rows work and some don't. If I was using the function wrong, then they would all be errors, right?

Thanks Community!!

Cheers,

Ren

iMac (27-inch, Late 2013), OS X Mavericks (10.9.1)

Posted on Dec 29, 2013 7:08 PM

Reply

Answer 1

SGIII

Level 8

37,744 points

Dec 29, 2013 7:16 PM in response to ren9666

Hi Ren,

Could you post what the table in which you are looking up values from looks like?

Generally a VLOOKUP that gets values from a second table looks something like this:

SG

Reply

Answer 2

ren9666 Author

Level 1

0 points

Dec 29, 2013 7:18 PM in response to SGIII

Edited my original post to include screen shots (I've had my iMac for three weeks LOL). Thanks SGIII

Reply

Answer 3

SGIII

Level 8

37,744 points

Dec 29, 2013 7:34 PM in response to ren9666

Your basic lookup would be something like this:

The formula I have in E2 is: =VLOOKUP(A2,Source Table::A:B,2,0)

Note that I have 'Use header names as labels' unchecked in Numbers > Preferences to make it a little easier to see which columns the formula refers to.

SG

Reply

Answer 4

SGIII

Level 8

37,744 points

Dec 29, 2013 7:44 PM in response to ren9666

wanting to add two VLOOKUP formulas together and that didn't work,

If you're trying to add Breakfast to Lunch, etc. one way approach you could take would look like this (I've only done Breakfast and Lunch but adding Dinner and Snack would be similar):

Formula: =VLOOKUP(A2,Source Table::A:B,2,0)+VLOOKUP(B2,Source Table::A:B,2,0)

As in Excel, VLOOKUP needs the value you want to look up, the range you are looking it up in, the column number in the range that contains the value you want to retrieve, and a parameter that specifies an exact match).

SG

Reply

Answer 5

ren9666 Author

Level 1

0 points

Dec 29, 2013 7:48 PM in response to SGIII

Thank you so much SGIII I really appreciate it!! That worked for adding the functions of breakfast and lunch items together. So by simplifying and using the values of 2 for the resulting value column and 0 for exact match the formulas work consistently. Being a new iMac user I thought the "bubble" and description labels were very cool, but I guess less is better.

Thanks for your help!!!

Reply