VBA using Excel's MATCH/INDEX function

[nvdunn]

HI there
I have 2 sets of data and the only unique identifiers I have in both data sets is:

FirstName
LastName
DOB

I am trying to match on these 3 criterias and also return their Address, Suburb, State, POst Code.

This is a very large data set and I am only expecting a small amount of matches however when I use this formula in excel {INDEX(CIDoB,MATCH($AQ2&$AR2&Q2,CIClientFName & CIClientLName & CIDoB,0)))} it just times out and takes FOREVER to update. SO i wanted to create a macro to loop through the data sets, identify the matches and return the names, DOB and addresses.



Address1
Suburb
State
Postcode

I want to do an IF statement that loops through over 400,000 rows that if FIRSTNAME, LASTNAME & DOB match from within my 2 named ranges, return all the data from sheet1 and append to the records on sheet 2 new.

Here is a sample of the Spreadsheet i am trying to loop through. I'm using named ranges for DOB FName LName for both worksheets. To get the one match in records, this is the Excel code i used
Q2 = Firstname on sheet 2
R2 = LastName on Sheet 2
P2 = DOB on Sheet 2
DOB = {INDEX(CADOB,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CADOB,0)))}
Add= {INDEX(CAAdd,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CADOB,0)))}
Suburb = {(INDEX(CASub,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CIDoB,0)))) }
PC= {INDEX(CAPC,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CADOB,0)))}
State = {INDEX(CAState,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CADOB,0)))}

I can't work out how to upload my excel spreadsheet any hints?
Here's a screen shot

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1297"]
<colgroup><col><col><col><col><col><col><col><col><col><col span="2"><col><col span="3"></colgroup><tbody>[TR]
[TD]Mger Code[/TD]
[TD]Manager[/TD]
[TD]Phone[/TD]
[TD]MobilePhone[/TD]
[TD]Email[/TD]
[TD]Firstname[/TD]
[TD]Surname[/TD]
[TD]AddressDetails[/TD]
[TD]Suburb[/TD]
[TD]State[/TD]
[TD]PostCode[/TD]
[TD]Ph1[/TD]
[TD]Mobile[/TD]
[TD]Premium[/TD]
[TD]DOB[/TD]
[/TR]
[TR]
[TD="align: right"]12345[/TD]
[TD]Paul Jones[/TD]
[TD][/TD]
[TD]0410000000[/TD]
[TD][/TD]
[TD]Joel [/TD]
[TD]Smith[/TD]
[TD]222 Fake St[/TD]
[TD]PHESSANT[/TD]
[TD]NSW[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD]0422555777[/TD]
[TD][/TD]
[TD="align: right"]5/10/1984[/TD]
[/TR]
[TR]
[TD="align: right"]45678[/TD]
[TD]SimonSmith[/TD]
[TD][/TD]
[TD]0410000001[/TD]
[TD][/TD]
[TD]Julie[/TD]
[TD]Broidy[/TD]
[TD]32 Lee Pl[/TD]
[TD]ST IVES[/TD]
[TD]QLD[/TD]
[TD]4022[/TD]
[TD][/TD]
[TD]0422555778[/TD]
[TD][/TD]
[TD="align: right"]17/10/1943[/TD]
[/TR]
[TR]
[TD="align: right"]12345[/TD]
[TD]John Little[/TD]
[TD][/TD]
[TD]0410000002[/TD]
[TD][/TD]
[TD]Cherish[/TD]
[TD]Laman[/TD]
[TD]12 Leonard Pl[/TD]
[TD]MARRICKVILLLE[/TD]
[TD]NSW[/TD]
[TD]2122[/TD]
[TD][/TD]
[TD]0422555779[/TD]
[TD][/TD]
[TD="align: right"]20/01/1986[/TD]
[/TR]
[TR]
[TD="align: right"]222888[/TD]
[TD]Noel Stevens[/TD]
[TD][/TD]
[TD]0410000003[/TD]
[TD][/TD]
[TD]Alisha[/TD]
[TD]Hayswoth[/TD]
[TD]77 Smith Dr[/TD]
[TD]APPIN[/TD]
[TD]NSW[/TD]
[TD]2560[/TD]
[TD][/TD]
[TD]0422555780[/TD]
[TD][/TD]
[TD="align: right"]7/03/1971[/TD]
[/TR]
[TR]
[TD="align: right"]12348[/TD]
[TD]Tanya Hill[/TD]
[TD][/TD]
[TD]0410000004[/TD]
[TD][/TD]
[TD]Alisha[/TD]
[TD]Hudson[/TD]
[TD]78 Smith Drive[/TD]
[TD]APPIN[/TD]
[TD]NSW[/TD]
[TD]2560[/TD]
[TD][/TD]
[TD]0422555781[/TD]
[TD][/TD]
[TD="align: right"]7/03/1971[/TD]
[/TR]
[TR]
[TD="align: right"]12345[/TD]
[TD]Paul Jones[/TD]
[TD][/TD]
[TD]0410000005[/TD]
[TD][/TD]
[TD]Rajeev[/TD]
[TD]Sharima[/TD]
[TD]100 Qurnaia Road[/TD]
[TD]PARKVILLE[/TD]
[TD]NSW[/TD]
[TD]2174[/TD]
[TD][/TD]
[TD]0422555782[/TD]
[TD][/TD]
[TD="align: right"]1/07/1965[/TD]
[/TR]
[TR]
[TD="align: right"]45678[/TD]
[TD]SimonSmith[/TD]
[TD][/TD]
[TD]0410000006[/TD]
[TD][/TD]
[TD]Cherish[/TD]
[TD]Levi[/TD]
[TD]4/8 Leonard Pl[/TD]
[TD]MARY[/TD]
[TD]NSW[/TD]
[TD]2122[/TD]
[TD][/TD]
[TD]0422555783[/TD]
[TD][/TD]
[TD="align: right"]20/01/1952[/TD]
[/TR]
[TR]
[TD="align: right"]12345[/TD]
[TD]John Little[/TD]
[TD][/TD]
[TD]0410000007[/TD]
[TD][/TD]
[TD]Brendan[/TD]
[TD]Foley[/TD]
[TD]21 Arthur Street[/TD]
[TD]HORNSBY[/TD]
[TD]NSW[/TD]
[TD]4740[/TD]
[TD][/TD]
[TD]0422555784[/TD]
[TD][/TD]
[TD="align: right"]5/10/1984[/TD]
[/TR]
[TR]
[TD="align: right"]222888[/TD]
[TD]Noel Stevens[/TD]
[TD][/TD]
[TD]0410000008[/TD]
[TD][/TD]
[TD]Axel[/TD]
[TD]Whiting[/TD]
[TD]25 Jersey St[/TD]
[TD]DEE WHY[/TD]
[TD]NSW[/TD]
[TD="align: right"]2099[/TD]
[TD][/TD]
[TD]0422555785[/TD]
[TD][/TD]
[TD="align: right"]5/10/1984[/TD]
[/TR]
[TR]
[TD="align: right"]12348[/TD]
[TD]Tanya Hill[/TD]
[TD][/TD]
[TD]0410000009[/TD]
[TD][/TD]
[TD]Johnno[/TD]
[TD]Ruler[/TD]
[TD]54 Gould Place[/TD]
[TD]WAHROONGA[/TD]
[TD]QLD[/TD]
[TD]4740[/TD]
[TD][/TD]
[TD]0422555786[/TD]
[TD][/TD]
[TD="align: right"]5/10/1984[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 1827"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Mger Code[/TD]
[TD]Manager[/TD]
[TD]MgerLastName[/TD]
[TD]ClientID[/TD]
[TD]ClientGivenName[/TD]
[TD]ClientLastName[/TD]
[TD]EntityName[/TD]
[TD]IsPrimary[/TD]
[TD]AddressLine1[/TD]
[TD]AddressLine2[/TD]
[TD]SuburbName[/TD]
[TD]PostCode[/TD]
[TD]State[/TD]
[TD]Country[/TD]
[TD]Email[/TD]
[TD]DateOfBirth[/TD]
[TD]Client2FirstName[/TD]
[TD]Client2LastName[/TD]
[TD]Client1 Match on DOB[/TD]
[TD]Address1[/TD]
[TD]Suburb[/TD]
[TD]Postcode[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD="align: right"]111222[/TD]
[TD]Paul[/TD]
[TD]Jones[/TD]
[TD="align: right"]5554443[/TD]
[TD]Joel [/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]222 Fake Street[/TD]
[TD][/TD]
[TD]PHESSANT[/TD]
[TD="align: right"]2000[/TD]
[TD]NSW[/TD]
[TD]AUSTRALIA[/TD]
[TD][/TD]
[TD="align: right"]5/10/1984[/TD]
[TD]Joel[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111222[/TD]
[TD]Paul[/TD]
[TD]Jones[/TD]
[TD="align: right"]5554443[/TD]
[TD]Julie[/TD]
[TD]Broidy[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]32 Lee Place[/TD]
[TD][/TD]
[TD]ST IVES[/TD]
[TD]4022[/TD]
[TD]QLD[/TD]
[TD]AUSTRALIA[/TD]
[TD][/TD]
[TD="align: right"]17/10/1943[/TD]
[TD]Julie[/TD]
[TD]Broidy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111222[/TD]
[TD]Paul[/TD]
[TD]Jones[/TD]
[TD="align: right"]5554443[/TD]
[TD]Cherish[/TD]
[TD]Laman[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]12 Leonard Place[/TD]
[TD][/TD]
[TD]MARRICKVILLLE[/TD]
[TD]2122[/TD]
[TD]NSW[/TD]
[TD]AUSTRALIA[/TD]
[TD]yes@yahoo.com[/TD]
[TD="align: right"]20/01/1986[/TD]
[TD]Cherish[/TD]
[TD]Laman[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111222[/TD]
[TD]Paul[/TD]
[TD]Jones[/TD]
[TD="align: right"]5554443[/TD]
[TD]Alisha[/TD]
[TD]Hayswoth[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]77 Smith Drive[/TD]
[TD][/TD]
[TD]APPIN[/TD]
[TD]2560[/TD]
[TD]NSW[/TD]
[TD]AUSTRALIA[/TD]
[TD][/TD]
[TD="align: right"]7/03/1971[/TD]
[TD]Alisha[/TD]
[TD]Hayswoth[/TD]
[TD="align: right"]7/03/1971[/TD]
[TD]77 Smith Dr[/TD]
[TD]APPIN[/TD]
[TD]2560[/TD]
[TD]NSW[/TD]
[/TR]
[TR]
[TD="align: right"]111222[/TD]
[TD]Paul[/TD]
[TD]Jones[/TD]
[TD="align: right"]5554443[/TD]
[TD]MARY[/TD]
[TD]HA[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]54 COLLEGE AVENUE[/TD]
[TD][/TD]
[TD]QUAKERS HILL[/TD]
[TD="align: right"]2529[/TD]
[TD]NSW[/TD]
[TD]AUSTRALIA[/TD]
[TD][/TD]
[TD="align: right"]12/03/1959[/TD]
[TD]MARY[/TD]
[TD]HA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111222[/TD]
[TD]Paul[/TD]
[TD]Jones[/TD]
[TD="align: right"]5554443[/TD]
[TD]WENDY[/TD]
[TD]HANS[/TD]
[TD][/TD]
[TD]No[/TD]
[TD]5 BRISBANE STREET[/TD]
[TD][/TD]
[TD]BRISBANE[/TD]
[TD="align: right"]4000[/TD]
[TD]QLD[/TD]
[TD]AUSTRALIA[/TD]
[TD][/TD]
[TD="align: right"]13/03/1967[/TD]
[TD]WENDY[/TD]
[TD]HANS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111222[/TD]
[TD]Paul[/TD]
[TD]Jones[/TD]
[TD="align: right"]5554443[/TD]
[TD]WENLI[/TD]
[TD]HA[/TD]
[TD][/TD]
[TD]No[/TD]
[TD]54 JONES AVE[/TD]
[TD][/TD]
[TD]BLACKBUTT[/TD]
[TD="align: right"]2529[/TD]
[TD]NSW[/TD]
[TD]AUSTRALIA[/TD]
[TD][/TD]
[TD="align: right"]13/03/1967[/TD]
[TD]WENLI[/TD]
[TD]HA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111222[/TD]
[TD]Paul[/TD]
[TD]Jones[/TD]
[TD="align: right"]5554443[/TD]
[TD]DANNY WAI KAI[/TD]
[TD]CATANIA[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]10 POLARIS PLACE[/TD]
[TD][/TD]
[TD]REEVSBY[/TD]
[TD="align: right"]2222[/TD]
[TD]NSW[/TD]
[TD]AUSTRALIA[/TD]
[TD][/TD]
[TD="align: right"]9/09/1949[/TD]
[TD]DANNY[/TD]
[TD]CATANIA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111222[/TD]
[TD]Paul[/TD]
[TD]Jones[/TD]
[TD="align: right"]5554443[/TD]
[TD]JOHNEE[/TD]
[TD]CATA[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD="colspan: 2"]10 UPPER BEACH Street[/TD]
[TD]Balgowlah[/TD]
[TD="align: right"]2766[/TD]
[TD]NSW[/TD]
[TD]AUSTRALIA[/TD]
[TD][/TD]
[TD="align: right"]9/09/1949[/TD]
[TD]JOHNEE[/TD]
[TD]CATA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Many thanks for all your help,
Nina

 

[Mohammad Basem]

Hi nvdunn,

You have provided many formulae with many Names, but unfortunately it is still not very much clear.

I would like to suggest that you give an example about what do you expect the VBA code to do. You have posted sample data, so you have to give more details about the named ranges that should be compared with and sample data of the expected results.

Also, use references from the sample data you have provided. For example, you have mentioned a field name LASTNAME, which does not exist. I think it is Surname, right?

To post sample file, upload it to any cloud drive (Dropbox, Google Drive, OneDrive, MediaFire…), make it shared and post the link.

 

[ttray33y]

try this on your Sheet1

''''''''''Main Function''''''''''''''''''''By: Edu Alester A. Otarra''''''''''
'''''''''December 2015''''''''''''
Sub EXTA()


Application.ScreenUpdating = False


'Declare variables "DIMS" for easy debudding
Dim DOB As Variant
Dim cname1 As String
Dim cname2 As String
Dim lastrow As Long
Dim i As Long
Dim wsheet As Worksheet
Dim dbase As Worksheet




'Set your variable values
Set dbase = ActiveWorkbook.Sheets("dbase")
Set wsheet = ActiveWorkbook.ActiveSheet
wsheet.Range("A2:J20000").ClearContents
cname1 = wsheet.Range("A1").Value
cname2 = wsheet.Range("B1").Value
lastrow = dbase.Cells(Rows.Count, 1).End(xlUp).Row


    


    DOB = wsheet.Range("C1").Value






'Start Row to Loop
For i = 2 To lastrow
    'Condition before Loop
    If dbase.Cells(i, 6) = cname1 And dbase.Cells(i, 7) = cname2 And dbase.Cells(i, 15) = DOB Then
        With dbase
            .Range(.Cells(i, 8), .Cells(i, 11)).Copy Destination:=wsheet.Range("A2000").End(xlUp).Offset(1, 0)
        End With
    End If
    
'Next Row to Loop
Next i




    
    Range("A1").Select


Application.ScreenUpdating = True


End Sub

 

[ttray33y]

or this,
note this is just a sample loop.

''''''''''Main Function''''''''''''''''''''By: Edu Alester A. Otarra''''''''''
'''''''''December 2015''''''''''''
Sub EXTA()


Application.ScreenUpdating = False


'Declare variables "DIMS" for easy debudding
Dim DOB As Variant
Dim cname1 As String
Dim cname2 As String
Dim lastrow As Long
Dim i As Long
Dim wsheet As Worksheet
Dim dbase As Worksheet


'Set your variable values
Set dbase = ActiveWorkbook.Sheets("dbase")
Set wsheet = ActiveWorkbook.ActiveSheet
wsheet.Range("H2:K20000").ClearContents
cname1 = wsheet.Range("E2").Value
cname2 = wsheet.Range("F2").Value
lastrow = dbase.Cells(Rows.Count, 1).End(xlUp).Row


    DOB = wsheet.Range("G2").Value
    
'Start Row to Loop
For i = 2 To lastrow
    'Condition before Loop
    If dbase.Cells(i, 6) = cname1 And dbase.Cells(i, 7) = cname2 And dbase.Cells(i, 15) = DOB Then
        With dbase
            .Range(.Cells(i, 8), .Cells(i, 11)).Copy Destination:=wsheet.Range("H2000").End(xlUp).Offset(1, 0)
        End With
    End If
    
'Next Row to Loop
Next i
    
    Range("A1").Select


Application.ScreenUpdating = True


End Sub

 

[ttray33y]

be sure to make a copy of your XL before running any Macro as theres no Undo after running a macro

 

[nvdunn]

Thank you so much for your help ttray33y that works well if I compare line by line. My problem is that I have 2 large data sets and the names will not be in order to match on, hence why I was trying to match the data using an Array range formula.

This is how I tweaked your code:



''''''''''Main Function''''''''''''''''''''By: Edu Alester A. Otarra''''''''''
'''''''''December 2015''''''''''''
Sub EXTA()




Application.ScreenUpdating = False




'Declare variables "DIMS" for easy debudding


Dim DOB As Variant
Dim cname1 As String
Dim cname2 As String
Dim lastrow As Long
Dim i As Long
Dim j As Long
Dim wsheet As Worksheet
Dim dbase As Worksheet




'Set your variable values
Set dbase = ActiveWorkbook.Sheets("dbase")
Set wsheet = ActiveWorkbook.ActiveSheet
'wsheet.Range("H2:K20000").ClearContents
'cname1 = wsheet.Range("E2").Value
'cname2 = wsheet.Range("F2").Value
'DOB = wsheet.Range("P2").Value
lastrow = dbase.Cells(Rows.Count, 1).End(xlUp).Row




j = 2

'Start Row to Loop
For i = 2 To lastrow 'row counter for CI Dbase info
'Condition before Loop
cname1 = wsheet.Cells(j, 5).Value
cname2 = wsheet.Cells(j, 6).Value
DOB = wsheet.Cells(j, 16).Value
If dbase.Cells(i, 6) = cname1 And dbase.Cells(i, 7) = cname2 And dbase.Cells(i, 15) = DOB Then
'MsgBox "MATCH"

With dbase
.Range(.Cells(i, 8), .Cells(i, 11)).Copy Destination:=wsheet.Range("AD2000").End(xlUp).Offset(1, 0)
End With
End If

j = j + 1 'increment to loop through each row
'Next Row to Loop
Next i

Range("A1").Select




Application.ScreenUpdating = True




End Sub


To resolve my issue, do I need to compare the information in 2 temporary arrays? i'm just not sure how i can analyse 2 sets of data when the matches will never occur on the same line. In my example, i made the matches appear in the same order (Stupid example sorry) but in reality I need to compare 2 data sets, of varying rows/columns and the matches will never be in the same row so can I use named ranges to try and match? I could do it in excel by using the excel function DOB = {INDEX(CADOB,MATCH($Q2&$R2&P2,CAFNAME & CALNAME & CADOB,0)))} whereby CADOB = ClientA DOB matching FirstName, LastName and DOB cells.

Thanks again for all your help you've got me thinking. I'm sure this is solveable.
many thanks
Nina