Formula for matching names and numbers between two sheets in Number

If the codes in the first table are Text (left-aligned by default but you may have changed the alignment manually) then you can do something like this:

In A2, filled down:

=XLOOKUP(TEXTBEFORE(C2,"-"),Table 1::B,Table 1::A,"not found")

If you got those leading 0s in the Code column by applying the 'Numeral System' Data Format with 4 places, then you can do this:

=XLOOKUP(VALUE(TEXTBEFORE(C1,"-")),Table 1::F,Table 1::A,"not found")

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

For future reference note that those are tables in Numbers, not sheets. A sheet in Numbers does not have cells. It is a blank canvas on which you can place multiple tables, charts, text boxes, and other objects.

More on the functions used here:

XLOOKUP - Apple Support

VALUE - Apple Support

SG

A simple way would be to use TEXTAFTER, something like this:

In D2 of the example, filled down:

=TEXTAFTER(C1,"-")

Then if you want to remove the formula from that column, select the cells, command-c to copy, followed by Edit > Paste Formula Results.

More on TEXTAFTER here:

TEXTAFTER - Apple Support

SG