... but I just want to create a function whereby if I enter a number (from 1-8 inclusive) the cell refers to another source and then displays, for example 'Sugar' (if I specify elsewhere that '1' = 'Sugar'). I know these are basics, but not for me! Thanks!
Numbers 3.5.3 iMac 21.5 inch Yosemite 10.10.3
so you want to lookup a name based on a value.
Something like this may work for you:
Create a second table (shown on the right),
call it "Product"
make the first row a header row
enter the information as shown (pasted below for convenience)
In the table on the left you can "get" the name by locating the value listed in the "Item ID" column in the table product then returning the value in the Name column of the same row. To do this... you use the function lookup() like this:
C2=IF(COUNTA(B2)>0,VLOOKUP(B2,ID:Name, 2, 0), "")
this is shorthand for, select cell C2, then type (or copy and paste from here) the formula:
=IF(COUNTA(B2)>0,VLOOKUP(B2,ID:Name, 2, 0), "")
select cell C2, copy
select all cells from C2 thru the end of column C, paste
ID | Name |
1 | Sugar |
2 | Flour |
3 | Butter |
4 | Brown Sugar |
5 | Cinnamon |
6 | Nutmeg |
7 | Baking Soda |
8 | Baking Power |
9 | Salt |
Thanks - I have done exactly this, but now get the following within the alert 'formula contains an invalid reference' - =IF(COUNTA(B2)>0,VLOOKUP(B2,#REF!, 2, 0), "")
Look at:
VLOOKUP(B2,#REF!, 2, 0)
the #REF! is not a valid reference. You should correct this. if you post a screenshot I can help. Compare this cell to one that works
1) Click on the triangle and report the message
2) click on the cell (but NOT the triangle) to show the formula in the cell. report the formula in the cell
The formula in Table 1 cell C2 should be:
=IF(COUNTA(B2)>0,VLOOKUP(B2,Product::A:B, 2, 0), "")
Ok, this now works, so thank you. But I've realised that what I actually need, is;
To have an option whereby if I enter a number (1 to 6 inc) in THE SAME cell, the outcome will therefore be shown in the adjacent cell.
So I guess it's the same formula, for each variable, but somehow 'condensed' in that same cell.
Also, I want to then repeat this, 8 times, so I can show 8 different themes, and then 6 different choices for each theme. I will then use a random number generator, whereby an 8-digit number will be generated, each number being from 1-6 inclusive. This will allow me to enter this random number and these formulas will display the 'outcome' of this random number!
This all sounds complicated (to me, anyway!), but I think it's just reputation of the same workings, no? Any advice appreciated!
Please post a screenshot of what you have.
Ok, so I realise I already have the option as regards the one cell. So now I would like to have the same formula in the next cell - in the first sheet, in the row marked 'B', referencing the 6 options as shown in the second sheet in the column marked 'B', then, of course to repeat therefore including all columns in the second sheet, A-G inclusive.
I know I just need to copy and paste the formula in some way, but I'm not sure exactly how, even though I know it's probably obvious and straightforward.. Please help! Thank you...
I did provide the explanation how to fill down. please review my post and pay particular attention to the lines:
select cell C2, copy
select all cells from C2 thru the end of column C, paste
Not sure if this is an 'if' function question...
