Vlookup/Index multiple criteria query

[bawpie]

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=Sheet1!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!

 

[bawpie]

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


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

I'd left out a bracket! Please disregard,

Many thanks!

 

[Lars-Åke Aspelin]

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=Sheet1!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!

Some modification to your formula gives the following:

=INDEX(Sheet2!C$2:C$4764,MATCH(1,(Sheet2!A$2:A$4764=Sheet1!B2)*(Sheet2!B$2:B$4764=Sheet1!A2),0))

Note the extra parentheses and the $-signs.
This is an array formula so make sure to enter it with
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke