+ Reply to Thread
Results 1 to 6 of 6

Index Match formula not returning correct results

  1. #1
    Forum Contributor
    Join Date
    11-13-2013
    Location
    California
    MS-Off Ver
    Excel 365 Apps for enterprise
    Posts
    180

    Index Match formula not returning correct results

    I have a formula I'm using in Column F, that is supposed to take Column A of Sheet 1, Match it to Column A of Sheet 2 and return the results from Sheet 2 Column B. It sure looks like it is working, but it's not returning the right results. For example, my Bar Backs should show Gaming, not Non-Gaming.

    Where did I go wrong on my formula?
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Index Match formula not returning correct results

    I note that the array argument for the INDEX() function starts on row 1. The "lookup_array" arguement for the MATCH() function starts on row 2. So when the MATCH() function finds "Bar Backs", it identifies it as the 22nd entry in rows 2:302 (spreadsheet row 23, which looks correct). The index functions counts down 22 rows starting in row 1 (instead of row 2), so it returns the value in row 22.

    Adjust one or the other of these ranges so they coincide correctly.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Index Match formula not returning correct results

    You have different start ranges right there :

    =INDEX(Sheet2!$B$1:$B$302;MATCH([@Position];Sheet2!$A$2:$A$302;0))

    change the highlited with 2

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Index Match formula not returning correct results

    =INDEX(Sheet2!$B$1:$B$302, MATCH(Table1[[#This Row],[Position]], Sheet2!$A$2:$A$302,0))
    Change the 2 as 1 (Sheet2!$A$1:$A$302)
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Index Match formula not returning correct results

    Hi
    $B$1:$B$302 ??? or $B$2:$B$302

  6. #6
    Forum Contributor
    Join Date
    11-13-2013
    Location
    California
    MS-Off Ver
    Excel 365 Apps for enterprise
    Posts
    180

    Re: Index Match formula not returning correct results

    Gosh, thanks! Such a silly little mistake!

+ 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. Index Match Returning Results for Filtered Out Data
    By hermes980 in forum Excel General
    Replies: 2
    Last Post: 08-27-2014, 12:19 AM
  2. Index/Match or VLookup returning Multiple Results
    By Stoppeh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2014, 08:31 PM
  3. [SOLVED] index match not returning all results
    By nellyc in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-06-2013, 11:22 AM
  4. index match not returning any results
    By nellyc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-03-2013, 03:29 PM
  5. [SOLVED] index match not returning all results
    By nellyc in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-25-2013, 08:06 AM
  6. [SOLVED] IFERROR+INDEX+MATCH Formula is not returning the correct value
    By bxk006 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-16-2013, 09:50 AM
  7. [SOLVED] SUMPRODUCT formula not returning correct results
    By CityInspector in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2012, 04: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