Vlookup & match

[amyylouise]

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???

 

[cyrilbrd]

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?
Adjust range and sheet name to fit your model.

 

[Aladin Akyurek]

CONTACTS

O2, control+shift+enter, not just enter, and copy down:

=IFERROR(MATCH($B2,IF(CUSTOMERS!$C$2:$C$6=$C2,CUSTOMERS!$B$2:$B$6),0),"")

A2, just enter and copy down:

=IF($O2="","",INDEX(CUSTOMERS!$A$2:$J$6,$O2,MATCH(A$1,CUSTOMERS!$A$1:$J$6,0)))

D2, copy across and down:

=IF($O2="","",INDEX(CUSTOMERS!$A$2:$J$6,$O2,MATCH(D$1,CUSTOMERS!$A$1:$J$6,0)))

 

[cyrilbrd]

CONTACTS
...A2, just enter and copy down:

=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...

 

[Aladin Akyurek]

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]

=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?

 

[Aladin Akyurek]

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]

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]

what about =IFERROR(SMALL(IF($A$15=$A$2:$A$6,IF($B$15=$B$2:$B$6,ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS($H15:H$15)),"")?

 

[Aladin Akyurek]

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