# Vlookup & match

#### amyylouise

##### New Member
CUSTOMERS TABLE

CONTACTS TABLE

WHAT I WANT TO DO:

In the CONTACTS TABLE when the Company and Branch entered is the same as the Company and Branch in the CUSTOMERS TABLE - pull the Account, Address, City, Province, Postal Code, and Country from the CUSTOMERS TABLE and enter this information in the respective cells in the CONTACTS TABLE

Help Please! I'm not sure which formula I should be using for this. Maybe VLOOKUP & MATCH???

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### cyrilbrd

##### Well-known Member
Hi, will you use data validation in column B and C?
Given in sheet custable:
 account company branch phone fax city 204 ABC this 269-555-3182 266-555-2030 here 66077 ABC that 215-555-3330 304-555-3363 there 3951 BCD this 239-555-3373 305-555-2631 else 35890 BCD that 240-555-2688 274-555-1622 what 36949 DEF that 220-555-1334 244-555-3272 where

<colgroup><col span="3"><col span="2"><col></colgroup><tbody>
</tbody>

and in sheet customer:
 account company branch phone fax city 66077 ABC that 215-555-3330 304-555-3363 there

<colgroup><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
formula in a2 is =IFERROR(INDEX(custtable!A\$2:A\$6,MATCH(1,(custtable!\$B\$2:\$B\$6=\$B2)*(custtable!\$C\$2:\$C\$6=\$C2),0)),"") Ctrl + Shift + Enter not just Enter on a PC or Command + Return on a MAC copied to column D, E, F...

Would that work for you?

##### MrExcel MVP
CONTACTS

O2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=IFERROR(MATCH(\$B2,IF(CUSTOMERS!\$C\$2:\$C\$6=\$C2,CUSTOMERS!\$B\$2:\$B\$6),0),"")
``````

A2, just enter and copy down:
Rich (BB code):
``````=IF(\$O2="","",INDEX(CUSTOMERS!\$A\$2:\$J\$6,\$O2,MATCH(A\$1,CUSTOMERS!\$A\$1:\$J\$6,0)))
``````

D2, copy across and down:
Rich (BB code):
``````=IF(\$O2="","",INDEX(CUSTOMERS!\$A\$2:\$J\$6,\$O2,MATCH(D\$1,CUSTOMERS!\$A\$1:\$J\$6,0)))
``````

#### cyrilbrd

##### Well-known Member
CONTACTS
...A2, just enter and copy down:
Rich (BB code):
``````=IF(\$O2="","",INDEX(CUSTOMERS!\$A\$2:\$J\$6,\$O2,MATCH(A\$1,CUSTOMERS!\$A\$1:\$J\$6,0)))
``````
...
I might (most likely) be wrong but shouldn't it be =IF(\$O2="","",INDEX(CUSTOMERS!\$A\$2:\$J\$6,\$O2,MATCH(A\$1,CUSTOMERS!\$A\$1:\$J\$1,0)))? same for D2...

##### MrExcel MVP
I might (most likely) be wrong but shouldn't it be =IF(\$O2="","",INDEX(CUSTOMERS!\$A\$2:\$J\$6,\$O2,MATCH(A\$1,CUSTOMERS!\$A\$1:\$J\$1,0)))? same for D2...

Thanks for the catch. That's what was intended.

#### cyrilbrd

##### Well-known Member
=IF(\$O2="","",INDEX(CUSTOMERS!\$A\$2:\$J\$6,\$O2,MATCH(A\$1,CUSTOMERS!\$A\$1:\$J\$1,0)))
Is this is more efficient correct and robust as compared with Index,Match(1,2cdts? it however requires one helper column but otherwise it would be better?

##### MrExcel MVP
Is this is more efficient correct and robust as compared with Index,Match(1,2cdts? it however requires one helper column but otherwise it would be better?

We would not want so many multiconditional Index/Match formulas. Hence the relative row calcs in column O. Yes, as such this is far more efficient.

#### cyrilbrd

##### Well-known Member
I find this very interesting. Although the model of the OP would not call for duplicates, what would you recommend to return the rows of all that would fit if there were more than one row fitting the description(s).
What about for unique search, would something like CSE =IFERROR(SMALL(IF(\$B\$2=Sheet2!\$B\$2:\$B\$6,ROW(Sheet2!\$B\$2:\$B\$6)-ROW(Sheet2!\$B\$2)+1),ROWS(\$O\$2:O2)),"") be efficient?
Here we would look for ABC solely but this would return row 2 and row 3...
My concern is for two (2) search ABC and this assuming that this combination appears more than once:
 account company branch phone fax city 204 ABC this 269-555-3182 266-555-2030 here 66077 ABC this 215-555-3330 304-555-3363 there 3951 BCD this 239-555-3373 305-555-2631 else 35890 ABC this 240-555-2688 274-555-1622 what 36949 DEF that 220-555-1334 244-555-3272 where

<colgroup><col span="3"><col span="2"><col></colgroup><tbody>
</tbody>

#### cyrilbrd

##### MrExcel MVP
I'd just stay with multiconditional MATCH... The task appears to be a multifield single record retrieval, not a (multifield) multirecord retrieval.

Replies
0
Views
104
Replies
2
Views
407
Replies
3
Views
665
Replies
6
Views
372
Replies
9
Views
862

1,195,582
Messages
6,010,577
Members
441,557
Latest member
Jbest23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back