Need a formula to match a number to a name in a large list in a separate workbook

Master workbook

A B C D
Mann Gerard 3 0.0001

Alphabetical Workbook
A B D L
Mann Gerard 0.0001 ?



I need a formula that matches the "C" column in the master workbook to column "L" in the alphabetical workbook. Im trying to match a share certificate number (column C) to last and first name in columns A and B. the kicker is that column C is dependent on column D as well. if that makes sense... so above would be - Gerard Mann has a share certificate in 0.0001 shares and that share certificate number is 3. I've been head butting with this one for weeks now and i need some help to get me in the right direction. Any help would be greatly appreciated.

Thanks in advance!

Hi

I'm not the greatest with Excel and I'm sure someone has a better solution, however what I would do is combine the values you have that are consistent in both spreadsheets - So in a new column in the master workbook add the formula =A1&B1&D1. In the next Cell after that copy the contents of Column C - Then copy the contents to a new sheet (i.e. If you added the formula to column E and the copied values to F - copy both columns to a new sheet)

Now in the Alphabetical Workbook in cell L, do the following: =A1&B1&D1. In both the master and alphabetical the value should read MannGerard0.0001. In the next cell M, click on formulas and find vLookup and click on the following;

Lookup value = L1
Table Arrey = Column A and B on the new worksheet
Column Index = 2
Range Lookup = False

I've added and example workbook for you.

ThanksWorkbook.xlsx

Thanks for your reply but I'm not sure that helps me exactly. But I've added an example workbook as to what i need it to look like.

Workbook (1).xlsx

in L5

=INDEX(Master!$C$6:$C$8,MATCH(Alphabetical!$A5&Alphabetical!$B5,Master!$A$6:$A$8&Master!$B$6:$B$8,MATCH(Alphabetical!L$2,Master!$D$6:$D$8,0)))

Enter with Ctrl+shift+Enter

Copy down

Note: change Super and Man round in MASTER

that worked perfectly for two sheets in the same workbook, but what if the two sheet were from two separate workbooks? I'm having trouble trying to fill in the formula with my workbook names.
the two names are:
'[Shares by Shareholder.xlsx]Master List'!

AND

('[Alphabetical Totals.xlsx]Sheet1'!

Im using this and its only bringing back a 0 value

=INDEX('[Shares by Shareholder.xlsx]Master List'!$C$6:$C$8,MATCH(Alphabetical!$A7&Alphabetical!$B7,'[Shares by Shareholder.xlsx]Master List'!$A$6:$A$8&'[Shares by Shareholder.xlsx]Master List'!$B$6:$B$8,MATCH(Alphabetical!L$7,'[Shares by Shareholder.xlsx]Master List'!$D$6:$D$8,0)))