Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match / Index multiple criteria return multiple results | Excel Worksheet Functions | |||
Index, match, multiple IFs query | Excel Worksheet Functions | |||
IF statement circumvent with Criteria or VBA,vlookup, index | Excel Discussion (Misc queries) | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Index/Match Multiple Criteria | Excel Discussion (Misc queries) |