Vlookup/Index multiple criteria query

B

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!
 
B

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!
 
L

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top