+ Reply to Thread
Results 1 to 19 of 19

Why isnt my index match formula working?

  1. #1
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Why isnt my index match formula working?

    What is wrong with my index match formula?

    Workbook4.xlsx

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Why isnt my index match formula working?

    it links to another workbook with loads of tabs that you havent reproduced
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: Why isnt my index match formula working?

    Quote Originally Posted by martindwilson View Post
    it links to another workbook with loads of tabs that you havent reproduced
    How did you know it links to another workbook? How can I make the formula work in just this workbook?

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Why isnt my index match formula working?

    well a bit of a clue is your formula looks like this
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    so with a bit of find and replace i set it up in another workbook like this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by martindwilson; 01-05-2014 at 12:58 AM.

  5. #5
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: Why isnt my index match formula working?

    How can I tell if it links to another workbook? How did you figure that out. I am a beginner at excel. Please explain.

  6. #6
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: Why isnt my index match formula working?

    How do I unlink it? And just make the formula work in the workbook you downloaded?

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Why isnt my index match formula working?

    see my last post

  8. #8
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: Why isnt my index match formula working?

    How can I manually get rid of links in the current document? Is there a link menu?

  9. #9
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: Why isnt my index match formula working?

    Did the Workbook get linked to another workbook because I copied and paste data from another workbook into the current workbook we are working on?

  10. #10
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Why isnt my index match formula working?

    probably!
    you can replace that formula with the shorter
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: Why isnt my index match formula working?

    i really dont get how the formula is linked to another workbook when all I see in the formula bar is:

    =IF(NOT(ISERROR(INDEX(Inventory_Sheet!$A:$H,MATCH(Log!$D2,Inventory_Sheet!$D:$D,0),MATCH(Log!G$1,Inventory_Sheet!$A$9:$H$9,0)))),INDEX(Inventory_Sheet!$A:$H,MATCH(Log!$D2,Inventory_Sheet!$D:$D,0),MATCH(Log!G$1,Inventory_Sheet!$A$9:$H$9,0)),"-")

    I am just trying to reference to the sheet not the formula you gave me.
    I am trying to use index match from the log sheet to grab the information from the inventory sheet into the paid and sold cells.

  12. #12
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Why isnt my index match formula working?

    when you copied to the test workbook it automaticaly created the links
    i certainly dont have this file path
    C:\Users\acer\Downloads\eWealth\invoices\2013\

  13. #13
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Why isnt my index match formula working?

    'C:\Users\acer\Downloads\eWealth\invoices\2013\[inventory2013.xlsx]September'!$A:$P, ...
    inventory2013.xlsx is the linked workbook (always in brackets)
    'C:\Users\acer\Downloads\eWealth\invoices\2013\ is the path to that workbook.
    Ben Van Johnson

  14. #14
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: Why isnt my index match formula working?

    I tried to manually type the code in, instead of copying and pasting:

    =IF(NOT(ISERROR(INDEX(Sheet2!$A:$H,MATCH(Sheet1!$D1,Sheet2!$C:$C,0),MATCH(Sheet1!G$1,Sheet1!$A$1:$H$1,0)))),INDEX(Sheet2!$A:$H,MATCH(Sheet1!$D1,Sheet2!$C:$C,0)*MATCH(Sheet1!G$1,Sheet2!$A$1:$H$1,0)),"-")

    But I still get in the compatibility report:

    Formula contains a link to a closed workbook.

    Why cant I type the code in manually?

  15. #15
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: Why isnt my index match formula working?

    how do I unlink the document, so I can write the formula without another document interfering?

  16. #16
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: Why isnt my index match formula working?


  17. #17
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: Why isnt my index match formula working?

    any other ideas? much appreciated.

  18. #18
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Why isnt my index match formula working?

    you haven't done anything to the original except copy some stuff
    so just junk Workbook4.xlsx and start again
    in the meantime you haven't actually said why the original doesn't work ,what goes wrong ?
    pick out each match section of the formula as i did in my sample enter those on a new sheet in the original and see what they return

  19. #19
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: Why isnt my index match formula working?

    figured it out. Please delete this thread. Thank you.

+ 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 formula not working
    By ea223 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-28-2023, 11:30 AM
  2. Correct/Working (Index,Match) formula not working between cells
    By barnerd in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 02-11-2014, 01:20 PM
  3. my if formula isnt working
    By zazzz in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-14-2013, 04:14 AM
  4. [SOLVED] index match formula not working
    By djmatok in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-19-2012, 11:12 AM
  5. this formula for selecting cells isnt working
    By twofootgiant in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2008, 03:29 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