Numbers Lookup Function

I am trying to make an estimate spreadsheet for my dental implant office.

I have made two tables:

Lookup table 1 with header and footer and cell B4 as the sum of column B. Column A cells are pop up cells with a list of items.
Table 2 is Items and their price

In table 1 I am trying to put a lookup function in B2. My problem is that this formula is not carried on down column B as I try to add mot items.

How do I get this formula copied on down the column?

iMac OS X ver 10.9

Numbers Version 3

Both the lates Mavericks.

Thanks.

Derek

User uploaded file

iMac, OS X Mavericks (10.9)

Posted on Oct 28, 2013 4:48 AM

22 replies

SGIII

Level 8

37,550 points

Oct 28, 2013 8:47 AM in response to Wayne Contello

SG. I suggested vlookup in the formula I posted earlier:

B1=IFERROR(VLOOKUP(A1,ItemList::A:B,2, 0),"")

Hi Wayne,

I saw that. That's why I was confused when you wrote that lookup (no caps) has one problem that you cannot specify exact match. But I see now you were responding to Derek's example where he used LOOKUP instead of your expected VLOOKUP.

Anyway, I didn't mean to jump in here. It's well covered. I'd hit Send Reply before seeing your first post.

Oct 28, 2013 9:09 AM in response to Wayne Contello

Wayne Contello wrote:

Jerry, I don't understand:

Sure, it can be made to work and starting a document from scratch you could use either, but they are both present for compatibility reasons.

SG. I suggested vlookup in the formula I posted earlier:

B1=IFERROR(VLOOKUP(A1,ItemList::A:B,2, 0),"")

Back in prehistoric spreadsheet times, there were multiple spreadsheet applications vying for supremacy. Some may have come up with "Improvements" not supported by the others, but doing basically the same thing. Later, new programs wanted to be compatible with members of all camps, so you could get a situation where LOOKUP and VLOOKUP do basically the same thing in a new app, and everyone would be happy because none of them had to rewrite existing documents.

Jerry

Oct 28, 2013 9:30 AM in response to Derekhandforth

I go back to my problem.

The subsequent row would automatically fill with the formula from the cell above in the older version of Numbers.

Since I have upgraded it no longer does.

Derek

Oct 28, 2013 9:33 AM in response to Derekhandforth

Derek,

I know the feature you are talking about. It only ever worked if the entire column has the same expression in all the body rows. When that is true, you get a new row filled with all the expressions as are in the other rows. If there is a break in the pattern, it doesn't work.

Jerry

Oct 29, 2013 3:47 AM in response to Derekhandforth

I still have problems, if any kind soul would like to look at the spreadsheet design and advise me, I can email it to them.

Contact me at derekhandforth@me.com

Thanks

Oct 29, 2013 7:11 AM in response to Derekhandforth

Derek,

Please post an screenshot of the problem table. If it is a huge table, make a duplicate of the table, and remove most of the rows so the table is 10 - 20 rows, then post a screenshot showing the problem. We like to keep everything in the forums so it helps other with same/similar problems.

ALSO... MAKE SURE THE FIRST ROW IS A HEADER if it does not contains the formula and you intend it to be a header

Here is a table set up such that when you add a row the formula automatically fills down:

Wayne

Oct 29, 2013 12:46 PM in response to Derekhandforth

Screen shots as requested. Both table have header and footer rows. Under the old Nmbers (pre Mavericks) the spreadsheet worked.

The example setup and formula used to fill in the formula in the row below when return was pressed in the last column.

Sheet 2

Sheet 1

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Numbers Lookup Function