In column A, there are 8 last names. Column B has a number for each name. Column C list the same people, but with first and last names. Column D has a number for each name. How can I match column C to column A, take the numbers attached to the name and input them into INDEX(B2:K11, MATCH(L3, B1:K1,0), MATCH(L2, A2:A11,0)) function which is connected to the table on Sheet 2 and then take the number from L4 and put it into column E of Sheet 1?

Ex.

The names in column A are in the correct order and won't change. For Mike Ingram, 4 is L2 and 5 is L3. L4 will equal 33(not shown). 33 will show in cell E3 on Sheet 1.

I'm using Numbers, but most Excel functions work. This is beyond my elementary understanding of spreadsheet programs, any help would be appreciated. Thank you.

CodePudding user response：

Have a look here for the L2 and L3 formulas:

I also added an example for L4 (which is different to your L4) to show how to refer to another table on the same sheet.

The reference works similarly for a second table on another sheet, and Numbers will assist you when you select the reference inside the formulae.

If you want to refer to the names row by row, L2 can be simplified with a direct cell reference (see cells E4 and E5 as examples)

I am confident you can adjust your L4 accordingly.

Assumption is that the family name is the second part of the name string after the [space] (and no duplicates in column A and column C)

You may also need to adjust ; with , due to regional settings if not done automatically by Numbers.

Edit 2: use column A as reference

=INDEX(Table 2::A$1:E$6;B8;XLOOKUP(REGEX(A8);C$1:C$8;D$1:D$8;0;2;1))