+ Reply to Thread
Results 1 to 3 of 3

Index/Match to Match entries on multiple criteria

  1. #1
    Registered User
    Join Date
    10-22-2013
    Location
    Auburn Alabam
    MS-Off Ver
    Excel 2013
    Posts
    11

    Index/Match to Match entries on multiple criteria

    Hi all!

    I'll just jump right into it. I am trying to match 2 separate spreadsheets of data based on 3 variables, and pull data from one column in one of those spreadsheets into the other. I will include the file below and add a more exact description later. I used the index/match tutorials online for matching based on multiple criteria, and it worked, but only to an extent. I am stumped, as several values that I can manually look up and find are not being brought into the new spreadsheet, despite the values matching on all 3 counts as far as I can tell. Rather than display the binary 0/1 value that is included in source data, only a N/A is displayed. I am hoping that one of you has had a similar problem and can point me in the right direction.

    For specifics, I am trying to match the "Sheet1" with the "Hedge or not" spreadsheet. The three variables I am matching on are "Company name at closing", column A in sheet 1, "Type", column D in Sheet 1, and "Closing Date", column I in Sheet 1. The data I am trying to pull from the sheet "Hedge or not" is column G in that sheet, titled "Hedge-1/Others-0". I am trying to move it to column K on Sheet 1, with the same column title.

    For an example of where I can't find why the data is not being pulled, look at row 20 on Sheet 1, with an entry for Abaxis, Inc. I have highlighted this entry. In column K, N/A is displayed, but on searching the "Hedge or not" spreadsheet, I can find the same entry in row 1498, again highlighted, for Abaxis, Inc. on the same date, with the same "Type". Yet on "Hedge or Not", a 0 is displayed for this entry. Can anyone help me explain why certain arbitrary entries are not being pulled over? This is not the only case I have been able to find, and just looking through Sheet 1, it is clear there are way too many N/A's compared to the source data.

    FYI, I have removed a significant portion of Sheet1 in order to meet the size requirements.

    I look forward to any responses, and thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    26,610

    Re: Index/Match to Match entries on multiple criteria

    The problem with row 20 is the entry in A20 has one extra blank on the end so Match fails. Some of the other #N/A errors could be a result of the same problem.

  3. #3
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Index/Match to Match entries on multiple criteria

    Hi
    The flaw is in the data. A20 in sheet1, the "Company Name at Closing" has an extra space at the end.
    Also on the sheet "Hedge or Not" in the same field has in A2515 value without point at the end.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] INDEX/MATCH with Multiple MATCH criteria ?
    By KomicJ in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-17-2015, 09:04 AM
  2. Sum Multiple Entries Through Index and Match
    By jmcconville18 in forum Excel General
    Replies: 2
    Last Post: 11-20-2014, 10:39 AM
  3. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  4. [SOLVED] Index Match using multiple criteria to match to
    By sacastiglia in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-25-2014, 03:46 PM
  5. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  6. Returning multiple entries with INDEX and MATCH
    By jessea in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-04-2011, 02:50 PM
  7. Match/Index with multiple entries
    By Jsaper in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-25-2010, 04:34 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1