
15. Re: how do you find and replace?
Barry May 10, 2012 3:07 PM in response to susannePAsusannePA 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

16. Re: how do you find and replace?
susannePA May 10, 2012 3:50 PM in response to BarryHere'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?

17. Re: how do you find and replace?
Barry May 10, 2012 4:23 PM in response to susannePAYes, 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

18. Re: how do you find and replace?
susannePA May 10, 2012 4:28 PM in response to susannePASo, 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.

19. Re: how do you find and replace?
Barry May 10, 2012 4:41 PM in response to susannePAOops! 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.
 CommandClick 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

20. Re: how do you find and replace?
Jerrold Green1 May 10, 2012 7:07 PM in response to susannePASusanne,
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 CommandC to copy it to the clipboard. Now Select the entire range that you want to extend the formula to and CommandV.
Probably the worst way to do this is by dragging the fill handle. It's fussy and it can be slow.
Jerry

21. Re: how do you find and replace?
susannePA May 10, 2012 7:16 PM in response to susannePAI'll try that; it sounds good. Will let you know how it goes. And thanks.

22. Re: how do you find and replace?
susannePA May 11, 2012 6:26 AM in response to Jerrold Green1IT 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.