Let's make some assumptions:
- The only column in Table 2 you care about is
- All of the data in both
Name columns is 100% correct and can be matched "exactly" (i.e. not fuzzily)
- The columns are labeled with a "header" row (the dark gray background by default)
- The tables are named
Table 1 and
- The data starts on row
2 in both tables
Therefore we are simply going to take the desired values from
Table 2::Website (Mac Numbers formula notation for "Column
Table 2") and plop them into
This is actually very easy to do, and it's one of those standard spreadsheet tricks that's great to have in your back pocket. An Internet search for something like "index match excel" and "vlookup excel" will give you tons of results.
Note for Microsoft Excel and Google Sheets users: write
! instead of
::, and put single-quotes
' around any table name with spaces in it. Should be the same result.
Solution 1 (general technique)
In the first row of
Table 1::Website, put this formula:
=IFERROR(INDEX(Website, MATCH(A2, Table 2::Name, 0), 1), "")
Unfortunately, because Numbers thinks it's smarter than you are, it won't let me paste this in -- you'll apparently have to type it by hand. Then click and drag the yellow dot on the bottom of the cell all the way down to the bottom.
Let's unpack this from the inside out.
Official documentation: http://help.apple.com/functions/mac/7.0/#/ffa59a83d3
MATCH takes the following arguments:
- Value to search for
- Range of values to search in
- Search "mode"
and returns the first position (1, 2, 3, etc.) in #2 that matches the value in #1. The documentation has more about argument #3, but 9 times out of 10 you will want to set it to
Official documentation: http://help.apple.com/functions/mac/7.0/#/ffa59b4edb
INDEX takes the following arguments:
- A range of cells to select from
- A row in that range to select
- A column in that range to select
- An area number to use in the selected cell
We want to select from
Table 2::Website, so that's what goes in to #1. The result of the
MATCH call above gives us the row number we want, so that goes into #2. There's only one column in that range, so we know we want to put
1 into 3. If #4 is omitted, it defaults to
1. Don't worry about this, just leave it out.
IFERROR takes the following arguments:
- A value to check for errors
- A replacement value if there's an error
This one is self-explanatory. If
INDEX fails, it will produce an error. Use this to replace those errors with blanks (or something else if you desire).
Solution 2 (shortcut)
You can simplify this query with a shortcut function:
=IFERROR(VLOOKUP(A2, Table 2::Name:Website, 3, 0), "")
What this does is "vertically looks up"
A2 in the range
Table 2::Name:Website and returns the value in column
3 that from the row it found. The
0 at the end specifies to look for an exact, rather than approximate, match. The
IFERROR part is identical.