Ah, have just managed to get it working now with the following:


=INDEX(Sheet2!C2:C4764,MATCH(1,(Sheet2!A2:A4764=Sh eet1!A2)*(Sheet2!B2:B4764=Sheet1!B2),0))

I'd left out a bracket! Please disregard,

Many thanks!

"bawpie" wrote:

Afternoon all,

There are already numerous queries relating to vlookups with multiple
criterias listed on this board but I'm afraid none really answer my own query.

I have 2 work sheets both with a list of names, birthdates and one of the
sheets holds a unique reference number that relates to the names. I am
trying to assign these numbers across to the other sheet but with little
success. There are about 200 names which need to be assigned numbers from a
list of 4,700 names (which is why I'm looking for a quicker way to do it!)

Basically what I'm trying to do is match the unique number across to the
other list of names by matching the surname and d-o-b as shown below:

Sheet 1

A B C
D-O-B Surname

Sheet 2

A B C
Surname D-O-B Ref

I tried the following arrayed formula in column C of sheet 1

=INDEX(Sheet2!C2:C4764,MATCH(1,Sheet2!A2:A4764=She et1!B2)*(Sheet2!B2:B4764=Sheet1!A2),0)

but I'm just getting #N/A's returned so I'm obviously doing something wrong
but I'm not sure what! Any help on the matter would be much appreciated!

Many thanks!