Closed Thread
Results 1 to 5 of 5

index/match formula not working

  1. #1
    Forum Contributor
    Join Date
    10-18-2012
    Location
    new york
    MS-Off Ver
    Office 365
    Posts
    177

    index/match formula not working

    Hi
    can anyone tell me what i have done wrong with this formula:
    =INDEX(Sheet1!$A$3:$T$500,MATCH(Sheet2!$A6,Sheet1!$A$3:$T$3,0),MATCH(Sheet2!$B$5,Sheet1!$A4:$A500,0))
    it is bring back a 0 value instead of the value i want it to bring back.
    thanks

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: index/match formula not working

    Reverse your formula to lookup first your rows and then your columns.

    =INDEX(Sheet1!$A$3:$T$500,,MATCH(Sheet2!$B$5,Sheet1!$A4:$A500,0),MATCH(Sheet2!$A6,Sheet1!$A$3:$T$3,0))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Contributor
    Join Date
    10-18-2012
    Location
    new york
    MS-Off Ver
    Office 365
    Posts
    177

    Re: index/match formula not working

    Amazing, thank you!!

  4. #4
    Registered User
    Join Date
    10-21-2008
    Location
    Delhi, India
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    45

    INDEX AND MATCH formula not working

    hi,

    I have 2 sheets 1. LR_CNF and 2. Summary

    LR_CNF sheet has container-wise loading data and we put BL no. against each container as
    they sail and BL is issued.

    in the Summary Sheet, under BL Status we intent to summarize BL only. if BL Coloumn is blank it should not be
    populated

    the formula used was working initially and now it stopped and not showing results after more
    containeers are inserted in this sheet.

    Kindly help is sorting the mistake if any in the formula or else advise.


    Thanks
    Ranbir
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    Re: index/match formula not working

    Administrative Note:

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

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

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