Index and Match Formula

MrPink1986

Board Regular
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.

Code:
=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

XPrimary Contacttest@gmail.com
XName
XPhone
XContact 2test2@gmail.com
XNameJohnDoe
XPhone1234
YAsset TypesAll
YPrimary Contacttest3@gmail.com
YName
YPhone
YContact 2test4@gmail.com
YNameJane Doe
YPhone456

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

barjoman

Board Regular
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

Board Regular
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

Board Regular
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

Board Regular
Your formula should be as follows:

Code:
=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

Board Regular
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:

Code:
=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

Board Regular
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.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top