Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Match function (2003 SP2)

    The enclosed spreadsheet contains a simple list in cells D4 to M4. I replicate that list in cells D6 to M6. In row 8 I use the MATCH function to determine where each item in the list should appear, but the function doesn't work properly. For example, the fourth item in the list is VIC_MFD, however when I use the MATCH function =MATCH(G6,$D$4:$M$4) where G6 contains the text VIC_MFD, the answer comes out at 10 instead of 4. Row 7 shows what the correct result should be and I have highlighted in yellow all the incorrect results.
    Why is the MATCH function returning the incorrect results?
    Thanks for your help.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match function (2003 SP2)

    You should specify that you want an exact match by adding 0 as third argument:

    =MATCH(D6,$D$4:$M$4,0)

    in D8 and fill right. Without 0 as third argument, MATCH finds the largest value that is less than or equal to the lookup value. This only works correctly if the lookup array is sorted in ascending order.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match function (2003 SP2)

    Thank you Hans, much appreciated again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •