Match two sets of columns simultaneously & if TRUE copy data from one cell into another?

[ReoMart]

Hi,

I'm quite new to formulas/VBA in Excel so apologies if this is a common question! Basically I have two lists of names. Column A is surname and Column B forename. Then a few more columns to the right that contian data related to those people. I have been given a second list of names with data, many of the poeple are already on the first list, and asked to merge it onto one spreadsheet. Is there a formula that can match the names (not all the names on both lists are the same) and if there is a match move the data relating to the names on the second list to empty cells to the right of the names from the first list?

I have tried a few formulas/conditional formatting but cannot seem to get around the issue of if e.g. V2 matches anything from A2:A236 AND W2 matches anything from B2:B236 then copy data from cell Y2 to cell U2.....?

Any help would be appreciated!

Many thanks

[mcmahobt]

Could you provide a sample workbook to play with? It is much easier for us to help you if we know which cells to reference in formulas. To attach a sample workbook, click Go Advanced --> Manage Attachments to upload.

[ReoMart]

Attachment 369233Hi,

Thanks for your reply - I have attached a sample workbook - what i need is to match column V to column A and column W to column B - and where there is a name that matches I need to copy the contents of the cell in column Y to it's matching name. I have inserted an empty column (U) to accomodate the data from column Y but my issue is that i can't seem to find a way of moving the data to it's matching name....I hope that makes sense!

Many thanks for any advice you can give me!

[mcmahobt]

Paste this into Y2 and drag down and across. Note that this is an array formula and must be confirmed with CTRL+SHIFT+ENTER:

=IFERROR(IF(INDEX(F:F,MATCH($V2&$W2,$A:$A&$B:$B,0))="","",INDEX(F:F,MATCH($V2&$W2,$A:$A&$B:$B,0))),"")

If your ranges are static, you can change the column references to the actual range of their respective tables to decrease processing time.

Hope this helps!

[ReoMart]

Attachment 369242Hi,

Thanks for this - I think i've almost got it! I've changed F:F to X:X as this is the data that i want to move based on whether the name matches....however it doesn't seem to be working quite right. I've attached the workbook as i've done it and, as you can see, cell U2 is correct as the % matches Surname1/Forename1 from columns V&W. However in cell U3 it has 97.18% but this doesn't match as Surname2/Forename2 in columns V&W is actually 88.73%?

Sorry to be a nuisance!

Many, many thanks.

[mcmahobt]

The formula was technically correct, just going backwards. Instead of matching from the V and W columns, we were matching the A and B columns. Try this in U2 and confirm with CTRL+SHIFT+ENTER and drag down.

I also changed the ranges for quicker processing time, but you can always change them back to column references if you like:

=IFERROR(IF(INDEX($X$2:$X$237,MATCH(A2&B2,$V$2:$V$237&$W$2:$W$237,0))="","",INDEX($X$2:$X$237,MATCH(A2&B2,$V$2:$V$237&$W$2:$W$237,0))),"")

Hope this helps!

[ReoMart]

Yes, that now seems to be wroking great! Thank you so much - your help has been greatly appreciated!

Kind regards