if, and, Vlookup functions - mutiple matchng

  • Thread starter Thread starter John R
  • Start date Start date
J

John R

Hi I have 2 workbooks each with mutiple columns.

WRKBk1:
First Name (B) Surname(C) Email Address (E)

Wrkbk2 (reference source):
First Name (A) Surname (B) Email address (C)

I enter first and surname in to wrkbk1. I am looking for a function that
will match match the two colulmns in WRKBk1 with the data in WRKBK2 and then
extract the email address from 2 and put it into column (e) in workbook1
 
The below array formula will look into the open workbook Book1.xls Sheet1 Col
A and Col B and retrive Col C value. In the workbook where you use this
formula cell B2 will have the first name and cell C2 will have the
surname..Please note that this is an array formula. Within the cell in edit
mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

=INDEX([Book1.xls]Sheet1!C1:C1000,MATCH(1,([Book1.xls]Sheet1!A1:A1000=B2)*([Book1.xls]Sheet1!B1:B1000=C2),0))

If this post helps click Yes
 
Once I entered the sheet/book names I ended up with this. It then returned
an N/A error

=INDEX('[Adviser List - email for
CPD.xls]Contacts'!C2:C1000,MATCH(1,('[Adviser List - email for
CPD.xls]Contacts'!A2:A1000=B14)*('[Adviser List - email for
CPD.xls]Contacts'!B2:B1000=C14),0))


The sheet which the names are typed into is: "workshop checklist."

This should then refer back to "Adviser List - email for CPD.xls]Contacts"
where the original names and emails are held.



Jacob Skaria said:
The below array formula will look into the open workbook Book1.xls Sheet1 Col
A and Col B and retrive Col C value. In the workbook where you use this
formula cell B2 will have the first name and cell C2 will have the
surname..Please note that this is an array formula. Within the cell in edit
mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

=INDEX([Book1.xls]Sheet1!C1:C1000,MATCH(1,([Book1.xls]Sheet1!A1:A1000=B2)*([Book1.xls]Sheet1!B1:B1000=C2),0))

If this post helps click Yes
---------------
Jacob Skaria


John R said:
Hi I have 2 workbooks each with mutiple columns.

WRKBk1:
First Name (B) Surname(C) Email Address (E)

Wrkbk2 (reference source):
First Name (A) Surname (B) Email address (C)

I enter first and surname in to wrkbk1. I am looking for a function that
will match match the two colulmns in WRKBk1 with the data in WRKBK2 and then
extract the email address from 2 and put it into column (e) in workbook1
Click to expand...
Click to expand...
 
Back
Top