Use 2nd sheet to reference "File" number and populate on 1st sheet.

Yes. This is pretty simple with an XLOOKUP()

The main thing to determine is how much of a match is enough.

it looks like the sheet 1 data has multiple components such as some code ('KINO GE6', 'KINO YB133", etc.) followed by some description ("1960s Party", "1980s Queueing Outside Club", etc."), a description ("Young People Dancing", "People Queueing", etc.) and a format ("HD.mov", "SD.mov", etc.)

The key is to get a consistent pattern to match across. If the first code is sufficient then I'd key off that, but you'd have to make sure that the entries are valid (for example, you can't match on the first two words if the lookup is "LSA242_SE18_SCREENER.mp4" since it's only a single word).

So maybe character count is sufficient - the first 10 characters, maybe?

Here's an example of XLOOKUP matching on the first 10 characters. In Sheet 1::B2:

=XLOOKUP(LEFT(C2,10)&"*",Table 2::$B,Table 2::$A,"",2,1)

Breaking this down, XLOOKUP() takes the first 10 characters of cell C2 (using LEFT(C2,10)) and appends a wildcard character "*". If you want a longer or shorter match, adjust the 10 figure..

This is used as the lookup key to search in column $B of the listings table.

For any match, it returns the corresponding value from column $A in the listings table.

The 2,1 at the end tells XLOOKUP to use a wildcard match (hence the * in the search term), and search top-to-bottom (so it will return the first item in the list that matches).

One approach you can try is XLOOKUP with the * wildcard, like this:

In B2, filled down: =XLOOKUP(LEFT(C2,13)&"*",Table 2::Name,Table 2::File,"NF",2)

This takes a specified number of characters from the name in Table 1 and adds the * wildcard, then uses that to lookup the File from Table 2. You may need to experiment with the number of characters in LEFT.

Use ; instead of , in the formula if your region uses , as the decimal separator.

XLOOKUP - Apple Support

LEFT - Apple Support

SG

Can this be used between Sheets, rather than Tables on the same Sheet?

The tables can be anywhere in the document.

Initially, Numbers matches the reference by table name - in this case, my example used 'Table 2::$B' meaning the B column from 'Table 2'. Numbers will search all sheets for any table called 'Table 2' and use that.

So you can cut the Table 2 and paste it onto a different sheet and everything will continue to work.

If you have multiple tables with the same name then things get a little trickier, but all is not lost... you just add another level of indirection to the table reference, in the form of:

=SheetName::TableName::$B

This will force Numbers to use the table called TableName on the SheetName sheet (obviously replace these with the actual sheet and table names :) ).

In this way your formulas can reference any cell in any table on any sheet in the same document.

As SGIII notes, Numbers will do all this heavy-lifting for you if you click to select the cell references you want when you're building your formula.

Sure, you can have your tables on different sheets.

In the formula editor, rather than typing out references, it generally works best to just navigate the range you want to reference and select it. If the range happens be in a table on another sheet then the formula editor will enter the correct reference for you.

SG