+ Reply to Thread
Results 1 to 4 of 4

Problems with Offset + Match function

  1. #1
    Registered User
    Join Date
    06-08-2017
    Location
    Manila
    MS-Off Ver
    2007
    Posts
    5

    Problems with Offset + Match function

    So I've got something I'm working on right now that I've been suggested to use a VLOOKUP on, and it looks like this:

    excel 3.PNG

    Right away, my problem is that whenever I type in 8 in the number of stores cell, it automatically returns a value of 0 regardless of what I put in # of compliant stores.

    On the other hand, with some of the lower number of stores, it also doesn't display correctly:

    excel 4.PNG

    See here, with 2 compliant stores / 3 total stores the resulting score should be a 3, but here it returns a 4. Where did things go wrong here?

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,299

    Re: Problems with Offset + Match function

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-08-2017
    Location
    Manila
    MS-Off Ver
    2007
    Posts
    5

    Re: Problems with Offset + Match function

    Sorry. Here's a copy of the file, with only the part I took a picture of.

    D8 (for # of stores) and E8 (for # of compliant stores) are the input cells, and B8 should be the result - which they're supposed to get the value from on the table on the right. The corresponding values aren't something I can change, as they're only given to me to make a function out of in Excel.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,420

    Re: Problems with Offset + Match function

    How about just this?
    =INDEX($L$6:$S$13,MATCH(D8,K6:K13,0),MATCH(E8,$L$5:$S$5,0))
    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. Replies: 1
    Last Post: 06-10-2015, 12:56 PM
  2. Problems applying INDEX-MATCH-MATCH function on other data
    By LennartB in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-13-2015, 05:33 AM
  3. Offset function with reference cell equal to the value of match function
    By cmurda in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-08-2014, 02:09 PM
  4. 2 Problems with offset/match function
    By aloconnor in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-18-2014, 03:21 AM
  5. Replies: 0
    Last Post: 01-19-2013, 01:35 PM
  6. Problems with offset function in VB
    By ksness in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2008, 12:04 PM
  7. [SOLVED] Offset function with nested match function not finding host ss.
    By MKunert in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-21-2006, 06:50 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