+ Reply to Thread
Results 1 to 12 of 12

Index and Match Error

  1. #1
    Forum Contributor
    Join Date
    09-01-2008
    Location
    Australia
    Posts
    108

    Index and Match Error

    Hi All,
    At Site A, I have the Qty for each product and store.
    At SiteB tab, I have SiteB qty as well. But at Column D on site B tab, I want to display Qty information from SiteA as well.
    i.e I want to know the qty from SiteA at Column D, based on the Combination of Prod and Store. (Using Index and Match Method)

    Any help is appreciated!

    Regards,
    Nironto
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Index and Match Error

    In D2 Cell of SiteB


    =IFERROR(INDEX(SiteA!C:C,MATCH(A2,SiteA!A:A,0)),"")


    Fill it down...


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Contributor
    Join Date
    09-01-2008
    Location
    Australia
    Posts
    108

    Re: Index and Match Error

    Thanks SixSense! It is close but I need the combo of prod and store because there are some (Products and store combo) in SiteB that does not appear in siteA.

    Regards,
    nironto

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Index and Match Error

    Quote Originally Posted by nironto View Post
    (Products and store combo) in SiteB.

    In D2 Cell of SiteB


    =IFERROR(LOOKUP(2,1/((Table_S657E4A5_AACL01[Prod]=[@Prod])*(Table_S657E4A5_AACL01[Store]=[@Store])),Table_S657E4A5_AACL01[Qty]),"")


    Drag it down...

  5. #5
    Forum Contributor
    Join Date
    09-01-2008
    Location
    Australia
    Posts
    108

    Re: Index and Match Error

    Appreciate again Sixsense. There is a reason for me to use Index and Match. I am using lookup currently. It works ok, but there will be a serious performance issue when it comes to massive no of records.
    Hence, I am changing all the links to using index and match instead. ^_^

    Regards,
    nironto

  6. #6
    Registered User
    Join Date
    09-08-2010
    Location
    Montenegro
    MS-Off Ver
    Excel 2010
    Posts
    82

    Re: Index and Match Error

    Try this formula in cell D2 at sheet SiteB:

    =INDEX(SiteA!$C$2:$C$29;MATCH([@Prod]&[@Store];SiteA!$A$2:$A$29&SiteA!$B$2:$B$29;0))

    Enter it as array (Ctrl Shift Enter) and copy down as needed.

    Of course, when you add more data to sheet SiteA, change references from $29 to appropriate number of rows.

  7. #7
    Registered User
    Join Date
    09-08-2010
    Location
    Montenegro
    MS-Off Ver
    Excel 2010
    Posts
    82

    Re: Index and Match Error

    P.S. - if there is no such a product in such a store at sheet SiteA (for example, in sheet SiteA, there is no product ABC151 in store A2), formula returns N/A error; if you want to make it visually nicer, then

    =IFERROR(INDEX(SiteA!$C$2:$C$29;MATCH([@Prod]&[@Store];SiteA!$A$2:$A$29&SiteA!$B$2:$B$29;0));"No quantity at site A!")

    or whatever other text you want.

    Please note that my Excel uses semicolon ; as arguments separator; if formula above does not work, please change semicolon with comma ( , instead of ; ):

    =IFERROR(INDEX(SiteA!$C$2:$C$29,MATCH([@Prod]&[@Store],SiteA!$A$2:$A$29&SiteA!$B$2:$B$29,0)),"No quantity at site A!")

  8. #8
    Forum Contributor
    Join Date
    09-01-2008
    Location
    Australia
    Posts
    108

    Re: Index and Match Error

    Thanks Forment!
    Your result is now very close.
    My problem is that I cannot use [Prod]&[Store].
    It said the name you entered is not valid. Reasons can include :
    -the name does not begin with a letter or underscore.
    -The name contains a space or other invalid chars

    I managed to get for the first row correct only if I use this. And then drag down
    =IFERROR(INDEX(SiteA!$C$2:$C$29,MATCH($A$2:$A$2&$B$2:$B$2, SiteA!$A$2:$A$29&SiteA!$B$2:$B$29,0)),"No quantity at site A!")

  9. #9
    Forum Contributor
    Join Date
    09-01-2008
    Location
    Australia
    Posts
    108

    Re: Index and Match Error

    Sorry guys. This looks like a tough nut to crack.

    Regards,
    Nironto

  10. #10
    Registered User
    Join Date
    09-08-2010
    Location
    Montenegro
    MS-Off Ver
    Excel 2010
    Posts
    82

    Re: Index and Match Error

    Quote Originally Posted by nironto View Post
    Thanks Forment!
    Your result is now very close.
    My problem is that I cannot use [Prod]&[Store].
    It said the name you entered is not valid. Reasons can include :
    -the name does not begin with a letter or underscore.
    -The name contains a space or other invalid chars

    I managed to get for the first row correct only if I use this. And then drag down
    =IFERROR(INDEX(SiteA!$C$2:$C$29,MATCH($A$2:$A$2&$B$2:$B$2, SiteA!$A$2:$A$29&SiteA!$B$2:$B$29,0)),"No quantity at site A!")
    Well, strange, that worked for me; I created that formula by clicking on cells A2 and B2 in sheet SiteB, and Excel automatically gave me references @Prod and @Store; I've tried now copy/paste the formula above into Excel, and it works, too.

    Though, if I change arrays within MATCH with single cells, this way:

    =IFERROR(INDEX(SiteA!$C$2:$C$29,MATCH(A2&B2,SiteA!$A$2:$A$29&SiteA!$B$2:$B$29,0)),"No quantity at site A!")

    it again works for me; try that. Just pay attention to have relative references, not absolute ones (A2, not $A$2), so to be able to copy formula down.

    BTW, just to clarify, my formula is the same one as offered by sixthsense in post #2; this is just the way to look for multiple criteria - you connect them with ampersand, &, in both instances, lookup value and lookup array, paying attention to their order.
    Last edited by froment; 03-19-2015 at 05:30 AM.

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Index and Match Error

    =IFERROR(INDEX(Table_S657E4A5_AACL01[Qty],MATCH(1,(Table_S657E4A5_AACL01[Prod]=[@Prod])*(Table_S657E4A5_AACL01[Store]=[@Store]),0)),"")

  12. #12
    Forum Contributor
    Join Date
    09-01-2008
    Location
    Australia
    Posts
    108

    Re: Index and Match Error

    Thanks Sixsense and froment!

+ 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] Ref# error on sum(index(match))
    By JayRab in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-14-2014, 04:17 PM
  2. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  3. Excel 2007 : Index-Match Error
    By mic84 in forum Excel General
    Replies: 5
    Last Post: 03-13-2012, 12:06 PM
  4. Index, Match, #REF Error
    By karstens in forum Excel General
    Replies: 9
    Last Post: 01-04-2011, 07:11 PM
  5. Index Match Error
    By dfab100 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-19-2007, 01:04 PM

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