Returning a cell value when matching two columns in different

[Malcolm Gill]

I have worksheets in two workbooks each with columns including forename and surname. If the person exists in workbook 2 then he/she will definitely be in workbook 1.

In workbook 2 I need a formula in the cells of column A which finds matches of forename and surname in workbook 1 and copies “mem_no” from worksheet 1 to worksheet 2.

Each column of data represents column A, B C etc.

Single value matching with VLOOKUP I can cope with but not 2 columns.

Can someone help?

Worksheet 1 example

mkey	mem_no	status	title	forename	surname
51355	857	Current	Mr	Laurie	Adamson
55701	1784	Current	Mrs	Edel	Aguero
51337	783	Current	Mrs	Rosemary	Allen
51472	1448	Current	Mrs	Sylvia	Allen
51453	1366	Current	Mr	Greg	Ansell
51454	1367	Current	Mrs	Tiggy	Ansell
51517	1574	Current	Mrs	Pam	Applin
51505	1546	Current	Mrs	Ann	Arthur
51566	1683	Current	Mr	Ken	Ashman

<tbody>
</tbody>

mem_no forename surname gkey1 Laurie Adamson 3811 Edel Aguero 3784 Sylvia Allen 3811 Rosemary Allen 3805 Greg Ansell 3811 Tiggy Ansell 3811 Pam Applin 3855 Ann Arthur 3853 Ken Ashman 3790 <tbody> </tbody>

<tbody>
</tbody>

 

[FDibbins]

Probably the simplest way to do this would be to add a helper column to WB 2, on the left, and combine the 2 names, then vlookup based on that.

If that is not feasible, try this...
=IFERROR(INDEX([file1]Sheet1!$B$2:$B$10,MATCH(B2&" "&C2,INDEX([file1]Sheet1!E$2:E$10&" "&[file1]Sheet1!$F$2:$F$10,0),0)),"Not Found")

 

[godsaaint]

This should work too (workbook 1 has to be open):

Change "workbook1" and "sheet1" to the respective real names and adjust the ranges accordingly.

CRTL-SHIFT-ENTER: =INDEX([Workbook1.xlsx]Sheet1!$A$2:$A$10,MATCH(B2&C2,[Workbook1.xlsx]Sheet1!$E$2:$E$10&[Workbook1.xlsx]Sheet1!$F$2:$F$10,0))

EDIT: If you would like it to work even with workbook 1 closed, check this link:

https://support.office.com/en-us/ar...668-ac6a-d7cca2a9b95f?ui=en-US&rs=en-US&ad=US

 

[FDibbins]

This should work too (workbook 1 has to be open):

Change "workbook1" and "sheet1" to the respective real names and adjust the ranges accordingly.

CRTL-SHIFT-ENTER: =INDEX([Workbook1.xlsx]Sheet1!$A$2:$A$10,MATCH(B2&C2,[Workbook1.xlsx]Sheet1!$E$2:$E$10&[Workbook1.xlsx]Sheet1!$F$2:$F$10,0))

EDIT: If you would like it to work even with workbook 1 closed, check this link:

https://support.office.com/en-us/ar...668-ac6a-d7cca2a9b95f?ui=en-US&rs=en-US&ad=US

My suggestion will work on closed workbooks

 

[godsaaint]

My suggestion will work on closed workbooks

You are using "[file1]" in your formula; I thought unless you specify the entire path of the file, it shouldn't work on closed workbooks. Or am I missing something?

 

[FDibbins]

You are using "[file1]" in your formula; I thought unless you specify the entire path of the file, it shouldn't work on closed workbooks. Or am I missing something?

Note that my suggestion is a regular formula, not an ARRAY formula, so the source need not be open. I used File1, but obviously that will need to be adjusted to whatever file is being referenced - which needs to include the path if the source file is not in the same folder as the destination

 

[Malcolm Gill]

Re: Returning a cell value when matching two columns in different files

Thanks for all your help guys. The only thing that's quicker than your replies in this world is the time it takes for money to slip through my fingers!

 

[FDibbins]

Re: Returning a cell value when matching two columns in different files

You must be married then?
LOL

 

[Malcolm Gill]

Re: Returning a cell value when matching two columns in different files

You're not wrong but by the age of 73 you'd think I'd have cracked it by now!

Have a good Christmas

Malcolm

 

[fmoreno]

Re: Returning a cell value when matching two columns in different files

I know this is an old thread but just wanted to say it just helped me tremendously... thanks everyone!!!