Vlookup/Index multiple criteria query
 

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!

Vlookup/Index multiple criteria query
 

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!

Vlookup/Index multiple criteria query
 

On Wed, 1 Jul 2009 07:27:45 -0700, 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=Sh eet1!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$476 4=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