+ Reply to Thread
Results 1 to 3 of 3

INDEX & MATCH different excel files

  1. #1
    Registered User
    Join Date
    06-17-2018
    Location
    Germany, München
    MS-Off Ver
    Excel 2016
    Posts
    1

    Question INDEX & MATCH different excel files

    Hi,

    I hope someone can help me with this since I really can't seem to get it right.

    I have two excel files in the same folder. Both of them contain customer numbers and one also some additional information to this number. I want to first see if the customer number exist in the other file and if so add the infoo from that row in the other file.

    To clarify:

    Excelfile 1: Have Customernr.
    Excelfile 2:Have Customernr + four columns with info in stringform, i.e can be both text and numbers.

    If the numbers match i want to add in Excelfile 1 the info from the four cells from the four columns from Excelfile 2 next to the Customer number.

    I tried to use: =INDEX([test.xlsx]Blad1!$B$2:$B$569;MATCH(G2;[test.xlsx]Blad1!$A$2:$A$569;0); 2) and then my idea was to repeat this formula four times to recieve one row. However this does not work.

    Have anyone an idea what could work?

    I would be forever grateful!

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: INDEX & MATCH different excel files

    When you say "this does not work", what exactly do you mean? What result do you get?

    BSB

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: INDEX & MATCH different excel files

    Looks to me like you either have the range wrong, or asking for excel to look into a column you have not defined?

    =INDEX([test.xlsx]Blad1!$B$2:$B$569;MATCH(G2;[test.xlsx]Blad1!$A$2:$A$569;0); 2)

    INDEX syntax is =INDEX(range to use, row number, column number)
    Your INDEX range is only column B, but you are telling excel to look in the 2nd column

    So maybe...
    =INDEX([test.xlsx]Blad1!$B$2:$C$569;MATCH(G2;[test.xlsx]Blad1!$A$2:$A$569;0); 2)

    OR...
    =INDEX([test.xlsx]Blad1!$B$2:$B$569;MATCH(G2;[test.xlsx]Blad1!$A$2:$A$569;0)

    If that still doesnt work, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Sum range accross files with index match
    By BjarkeSK in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2018, 04:01 PM
  2. [SOLVED] INDEX/MATCH using two files
    By Mr.rE in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-31-2017, 04:15 PM
  3. VBA Code for Index/Match/Match to input into excel based on 2 criteria inside form
    By Carl Fisher in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2017, 12:11 PM
  4. index-match between two huge files
    By is2_egypt in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2016, 09:59 AM
  5. [SOLVED] Index Match Issue linked with Other files
    By Fletch74 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-27-2015, 10:28 PM
  6. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  7. Index/Match from 2 sources of files
    By rickyilas in forum Excel General
    Replies: 1
    Last Post: 06-26-2012, 05:49 PM

Tags for this Thread

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