1 2 Next 22 Replies Latest reply: May 11, 2012 6:26 AM by susannePA Go to original post
• Level 7 (29,210 points)

susannePA wrote: Also - doesn't the column designation refer to the whole column and not just the cell it is in?

Numbers will interpret partial references as appropriate.

In LOOKUP(R,F,Q)

R: requires a specific, single search value, so this will be interpreted as a reference to Rn, where n is the same row as the formula occupies.

F: requires a range of cells, so F will be interpreted as a reference to all cells in column F.

Q: requires a range of cells (equal to the range for the second argument, F, so Q will be interpreted as a reference to all cells in column Q.

Regards,

Barry

• Level 1 (0 points)

Here's what I want to do - look up values in one column, match them with another column, find the corresponding result in another column and place that in the column with the formula - all the way down the entire column.  Can that be done?

• Level 7 (29,210 points)

Yes, but in order to "place that (result) in the column with the formula - all the way down the entire column," each cell in "the entire column" must contain the formula. Formulas cannot "place" values in cells other than their own; formulas can "retrieve" values from other cells, do calculations with those formulas, and place the result in the cell containing that iteration of that formula. In my earlier example, each cell in column C of the middle table contains the formula: =LOOKUP(A,Data :: \$A,Data :: \$B)

In C2, the formula, stated in English, says, "look for the value in A2 (of this table); look for a 'close match' in column A of the table 'Data'. When you find the largest value less than or equal to it; return the value from the same row of column B of the table 'Data'."

In C9, the same formula, stated in English, says, "look for the value in A9 (of this table); look for a 'close match' in column A of the table 'Data'. When you find the largest value less than or equal to it; return the value from the same row of column B of the table 'Data'."

Regards,

Barry

• Level 1 (0 points)

So, there's no shortcut to placing the formula in the entire column of >4000 rows; I just have to hold the copy button down that long.  Oh well, thanks and you and Jerry are my heroes.  Imost appreciate your help.

• Level 7 (29,210 points)

Oops! Jumped directly to page 2 and missed this one. Sorry.

susannePA wrote:

Is there a shorthand way to incidate the entire column as this sheet table has over 4,000 rows?

No, but there is a shortcut to selecting the whole column (minus the rows above the first instance of the formula) before filling down. Example is for column C, with the formula entered into C2:

• Click on any cell to activate the table and display the column and row reference tabs.
• Click on the reference tab for column C to select all of column C.
• Command-Click on C1 to remove it from the selection.
You now have C2 and all of the cells below it in column C selected.
• Go Insert (menu) > Fill > Fill Down.
Numbers will fill the formula into all of the selected cells.

Regards,

Barry

• Level 7 (29,925 points)

Susanne,

Another way to fill an entire column is to Paste to it.

After entering your expression in the first body row, and hitting Enter or Tab, then click back on the cell with the formula in it and Command-C to copy it to the clipboard. Now Select the entire range that you want to extend the formula to and Command-V.

Probably the worst way to do this is by dragging the fill handle. It's fussy and it can be slow.

Jerry

• Level 1 (0 points)

I'll try that; it sounds good.  Will let you know how it goes.  And thanks.

• Level 1 (0 points)

IT WORKED!!! Wow, As you know I've been struggling with this all week, on and off.  Finally, between you and Barry, poblem solved.  Thank you.

1 2 Next