How would I write a formula that fills a cell with a value from a different table based on the row number of the other table matching a cell value in the current table. It's simple lookup for a (filemaker) database but I'd like to get this working in this spreadsheet. Both table are on the same page.
Thanks-
MacBook Pro, OS X Mavericks (10.9)
Hi jtice,
Here's one way to do that, using the INDIRECT function:
The formula in A2 of Table 1, copied down is:
=INDIRECT("Table 2::B"&B2)
The values in column B are the row numbers used to look up values from Table 2.
As you no doubt aware there are VLOOKUP, HLOOKUP, and LOOKUP functions that match on value, rather than row number, if you want to do that kind of lookup.
SG
The LOOKUP function does what I need. I had to manually add "$" to cell ranges to make them fixed. Now I need to figure out how to get rid of the error alerts in the rows which do not yet contain data. Is the LOOKUP function the best way or is there a more robust way of doing this?
I have no idea about other languages. You just get what you would normally view on the site from your locale.
It's hard to miss the other languages. This is prominent on the landing page of the html help:
Anyway, I'm guessing Site Sucker by default grabs only pages related to the currently selected language, and I hope my app will too.
SG
Another choice would be =OFFSET(Table 2::B$1,B2-1,0)
Either function will do the job. The advantage of OFFSET is it will keep working if you change the name of "Table 2" or move it to another sheet. With INDIRECT, you would have to go back in and edit the formula.
I think I will need to us one of the lookup functions (VLOOKUP, HLOOKUP, LOOKUP) because the row number I'll be looking up in Table 1 will match a variable in the current row of Table 2, and will not be related by relative position.
I am just trying to get my head around this application for the first time. I've used Excel but my real experience is Filemaker, so I tend to think in terms of triggers, keys, relationships rather than position. I need to read the manual I suppose.
Thanks for your help!
Maybe an example will help. Or maybe a short mock-up of the two tables with the answers typed in so we know what you are trying to achieve. The solutions given above match your initial description of the problem. Now I'm not sure what you are asking. Written language is not very precise; examples are often very helpful. I don't want to waste either of our times with another solution to the wrong problem.
JT,
Another way to suppress the error display is to eliminate the source of the error. You could write:
=IF(LEN(D)>0, Your original expression, "")
Now the column E expression will wait until you have entered a value in column D before displaying a value.
Jerry
Got it! Thanks a bunch! You'd think there would be a simple menu item or preference to turn off alerts since that's going to happen anytime you set up a spreadsheet for data that's to be entered in the future.
Is there a comprehensive PDF manual for this new version (3.0.1)? I've looked and all I'm finding is '08 and '09 manuals.
JT,
The V3 manual is HTML. You can open the help document in your browser by clicking the link in the Help menu.
I have used "Site Sucker" to make a local copy of the web version that can be used off-line.
Further to the Error trap and Error Display Preferences, Error Flags serve an important purpose, so trap them wisely. Best to avoid doing that especially while editing the expressions in a table.
Jerry
I have used "Site Sucker" to make a local copy of the web version that can be used off-line.
Hi Jerry,
That's a good idea. I was thinking of trying something like that with DEVONthink site downloading capability. I'm curious, when you "suck" the site down is it easy to retrieve just the English? Or do you end up with all the languages?
SG
Jerry, I thought that might be the case with the manual. I didn't have much luck with the app's help feature. It uses one of those fuzzy searches and I get a lot of hits that aren't relevant. I'll try the site-sucker and if that's an improvement. Wish they had a PDF manual though, because you can search precisely.
Thanks again-
JT,
I miss the search function of the PDF in Preview.app. I have to say though that I've learned some things by poking around in the Numbers 3 Help manual/site. With so much less content to cover, what's left is easier to find.
Jerry
To suppress the error alerts you could use IFERROR like this:
Formula in E2, filled down:
=IFERROR(VLOOKUP(D2,Table 1::A:C,3,0),"")
SG
Yes, OFFSET is better. I think INDEX also works:
=INDEX(Table 2::B,B2)
(I never quite get used to the fact that in most situations I end up having to subtract 1 when using OFFSET.)
SG
I have no idea about other languages. You just get what you would normally view on the site from your locale.
Jerry
Pulling values from another table
