+ Reply to Thread
Results 1 to 12 of 12

ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for others

  1. #1
    Registered User
    Join Date
    01-12-2016
    Location
    USA
    MS-Off Ver
    2003
    Posts
    5

    ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for others

    I have been putting together an Excel file to help our customers better understand our contract pricing (yes, I know this is a problem in and of itself...) with them. To do this, I decided that I was going to use a lot of Index+Match+Match type formulas to basically return a lot of data based on only entered 2 cells of information.

    For the purpose of my problem I will only show a couple.

    The 2 columns Collection and SKUs are returning a match for the wrong value, but only on a couple of different entries which is really confusing me. There are no duplicate entries in my worksheet to cause Index to be confused on what to return.

    They are both using Index+Match equations.

    Here are the 2 columns where the formulas are placed:

    Where the formulas are entered, columns D and E - http://i.imgur.com/CduHty3.png

    The data that the Index is for the match to look in - http://i.imgur.com/Ap7t8OB.png


    Here are the equations:

    SKU Formula - http://i.imgur.com/F8YbsZe.png

    Collection Formula - http://i.imgur.com/rxw8GHL.png


    As you can see - when I type in Modern Blaze it will return the collection name and SKU value for Milano...yet when I type in Milano it comes back with the correct collection name and SKU value for Milano like it is supposed to.

    A couple other examples are also on the pictures.

    Any thoughts...?

    Here are the formulas typed out in case any one wants to mess with them to assist.

    SKU FORMULA

    =IF(ISERROR(INDEX('Unit Costs'!$Q$1:$Q$98,MATCH(B2,'Unit Costs'!$O$1:$O$98,0))),"",INDEX('Unit Costs'!$Q$1:$Q$98,MATCH(B2,'Unit Costs'!$O$1:$O$98,0)))


    COLLECTION FORMULA

    =IF(ISERROR(INDEX('Unit Costs'!$P$1:$P$98,MATCH(B2,'Unit Costs'!$O$1:$O$98,0))),"",INDEX('Unit Costs'!$P$1:$P$98,MATCH(B2,'Unit Costs'!$O$1:$O$98,0)))

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for ot

    Pictures aren't very helpful here, because we can't copy/paste values from the picture into our own workbooks for troubleshooting.

    Can you attach an actual sample workbook?
    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Registered User
    Join Date
    01-12-2016
    Location
    USA
    MS-Off Ver
    2003
    Posts
    5

    Re: ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for ot

    I would but it has a lot of pricing information thrown throughout -- but you are telling me there is no way to look at the equation and the pictures to see any issue?

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,895

    Re: ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for ot

    It would help a lot!!! It's not easy when you can't see everything at once. Just delete any extraneous stuff and post the sheet.
    Glenn



  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for ot

    Nope, I can't see anything wrong with the formulas (or the data) shown in the pictures you posted.
    In fact, I don't even see a formula that is returning an incorrect result...

    If you can work out a trimmed version of the file (still displaying the problem).
    We only need to see a few rows of data.
    Replace real names/numbers with bogus made up information.

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for ot

    Quote Originally Posted by Jonmo1 View Post
    Pictures aren't very helpful here, because we can't copy/paste values from the picture into our own workbooks for troubleshooting.
    Also, many people here are unwilling/unable to view documents stored on a 3rd party file sharing site.
    But we tend to trust documents more if they are actually stored on excelforum.com's own server.

  7. #7
    Registered User
    Join Date
    01-12-2016
    Location
    USA
    MS-Off Ver
    2003
    Posts
    5

    Re: ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for ot

    For Help.xls

    Here! Hopefully this should help.

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for ot

    You have extra spaces in alot of the cells in the Units Costs tab, column A.
    Example, A56 is actually "Modern Blaze " instead of "Modern Blaze"
    But the value in B2 on Sheet2 is "Modern Blaze"

    "Modern Blaze" does NOT match "Modern Blaze "

  9. #9
    Registered User
    Join Date
    01-12-2016
    Location
    USA
    MS-Off Ver
    2003
    Posts
    5

    Re: ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for ot

    Is there an easy way for me to fix this? Is this mostly what the =TRIM formula is for?

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for ot

    Quote Originally Posted by kuunamatata View Post
    Is this mostly what the =TRIM formula is for?
    Yes, TRIM would be ideal for that.

    On the Units Cost tab, in any available column (say G)
    In G2 and filled down to the end of your data put
    =TRIM(A2)

    The copy column G, and Paste Special - Values on top of column A.

  11. #11
    Registered User
    Join Date
    01-12-2016
    Location
    USA
    MS-Off Ver
    2003
    Posts
    5

    Re: ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for ot

    Geez. I feel so dumb now. I knew it had to be something as simple as this.

    Thanks for your help, Jonmo1.

  12. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: ISSUE + MATCH + MATCH formula returning wrong value for some cells, but correct for ot

    You're welcome.

+ 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 returning correct results
    By JennOlsen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-30-2015, 02:59 PM
  2. Validating 2 cells goes wrong, finds first match instead of exact match.
    By EricNL in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2015, 08:38 AM
  3. Issue: Only returning 1st match on Index/Match
    By tbr2891 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2014, 11:54 AM
  4. Replies: 3
    Last Post: 10-10-2014, 08:14 AM
  5. [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
  6. Index Max Match formula returning wrong value
    By bk77 in forum Excel General
    Replies: 4
    Last Post: 03-25-2009, 02:17 PM
  7. Match() returning wrong row reference
    By dee101 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-20-2008, 07:15 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