Index and Match Formula

[MrPink1986]

Hi,

I am trying to create a formula where I need two criteria to match to pull back a value. On my sheet Global Contacts I have a list of Vendors and a number of fields with contact information- I want to pull back the contact information for each vendor under the below headers - the unique piece of information is the vendor and on my main sheet when I input x I get all the information below and also when I input Y it updates accordingly.

Primary Contact

Primary Name

Primary Phone

Secondary Contact

Secondary Name

Secondary Phone

Teritary Contact

Teritary Phone

<colgroup><col></colgroup><tbody>
</tbody>

I am trying the below formula - I am putting my vendor information on to my main page in cell F3. My contact information is contained in the Global Contacts sheet from A-G. The below formula is trying to pull back the Primary Contact and I understand I will need to ammend to pull back each field listed.

=INDEX('Global Contacts'!A:G,MATCH(1,('Global Contacts'!A:A=Main!F3)*('Global Contacts'!A:C=Main!E16),0),3)

Below is a sample of the data I am working with

X	Primary Contact	test@gmail.com
X	Name
X	Phone
X	Contact 2	test2@gmail.com
X	Name	JohnDoe
X	Phone	1234
Y	Asset Types	All
Y	Primary Contact	test3@gmail.com
Y	Name
Y	Phone
Y	Contact 2	test4@gmail.com
Y	Name	Jane Doe
Y	Phone	456

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[barjoman]

Are you able to post a sample file? I understand what you are trying to do, but I'm having trouble visualizing how you are currently doing it and what the issue is.

 

[MrPink1986]

I don't seem to be able to attach files.

Are you able to post a sample file? I understand what you are trying to do, but I'm having trouble visualizing how you are currently doing it and what the issue is.

This is the main page info - variable is in F1
Formula from above should go into the cells beside the

A F
Please select which vendor you would like to look up from the list provided Vendor1

Primary Contact #VALUE!
Primary Name
Primary Phone
Secondary Contact
Secondary Name
Secondary Phone
Teritary Contact
Teritary Phone
Asset Type

This is the information I want to pull back from Global Contacts sheet into the main tab based no the variable in column A

A B C
Vendor1 Asset Types Bonds
Vendor1 Primary Contact test@gmail.com
Vendor1 Primary Name John Doe
Vendor1 Primary Phone 1234
Vendor1 Secondary Contact test2@gmail.com
Vendor1 Secondary Name Jane Doe
Vendor1 Secondary Phone 0001222
Vendor2 Asset Types Equity
Vendor2 Primary Contact test3@gmail.com
Vendor2 Primary Name Bob Hope
Vendor2 Primary Phone 1111111
Vendor2 Secondary Contact test4@gmail.com
Vendor2 Secondary Name Billy Jean
Vendor2 Secondary Phone 111233

 

[MrPink1986]

Any help on this would be greatly apreciated- I think the formula is almost correct but cant seem to fully understand why it is still returning N/A

 

[barjoman]

Your formula should be as follows:

=INDEX('Global Contacts'!A:G,MATCH(1,('Global Contacts'!A:A=Main!F3)*('Global Contacts'!B:B=Main!E16),0),3)

With Global Contacts A:A you are matching the vendor name to cell Main F3, and with Global Contacts B:B you are matching the contact type (email, phone, etc) with cell Main E16.

Finally, your formula is an array formula, so you must use CTRL+SHIFT+ENTER when confirming the formula instead of just ENTER.

Let me know if this works. If not, there is an alternate solution (which in my mind is better because it avoids the use of arrays).

 

[MrPink1986]

Thank you this has worked as I wanted.
I am curious to see what the alternate soultion would be.

Your formula should be as follows:

=INDEX('Global Contacts'!A:G,MATCH(1,('Global Contacts'!A:A=Main!F3)*('Global Contacts'!B:B=Main!E16),0),3)

With Global Contacts A:A you are matching the vendor name to cell Main F3, and with Global Contacts B:B you are matching the contact type (email, phone, etc) with cell Main E16.

Finally, your formula is an array formula, so you must use CTRL+SHIFT+ENTER when confirming the formula instead of just ENTER.

Let me know if this works. If not, there is an alternate solution (which in my mind is better because it avoids the use of arrays).

 

[barjoman]

Thank you this has worked as I wanted.
I am curious to see what the alternate soultion would be.

Glad it worked. The alternative is to concatenate the Vendor and Contact Type (I.e. primary email, etc) on your Global contacts sheet. Then do a vlookup or Index using the same concatenated fields from your Main sheet.

I avoid the use of array formulas because in my experience performance really suffers for large datasets.